tsql - SQL - insert into QUOTENAME(@DatabaseName) -

tsql - SQL - insert into QUOTENAME(@DatabaseName) -

declare @databasename varchar(30), @article varchar(16), @partnerid int set @databasename = 'demo' set @article = 'article1' set @partnerid = 1 insert quotename(@databasename) + '.dbo.move(article, partner, note)' select @article, @partnerid, 'note'

i have , error is:

msg 102, level 15, state 1, line 7 wrong syntax near '+'.

i try:

declare @sql nvarchar(max) declare @databasename varchar(30), @article varchar(16), @partnerid int set @databasename = 'demo' set @article = 'article1' set @partnerid = 1 set @sql = n'insert '+ quotename(@databasename) + n'.dbo.move(article, partner, note)' + n'select ' + @article + n', ' + @partnerid + n', '''note''' ' exec @sql

but error:

msg 102, level 15, state 1, line 9 wrong syntax near 'note'.

if going utilize dynamic sql (which should avoided if have alternatives), it's still thought utilize sp_executesql , parameters, , using replace build statements rather concatenation saves lot of headaches debugging proper escaping. in example:

set @sql = replace( 'insert $database.dbo.move(article, partner, note) values (@article, @partner, ''note'')', '$database', quotename(@databasename) ); print @sql; -- check we've produced execute sp_executesql @stmt = @sql, @params = n'@article varchar(16), @partner int', @article = @article, @partner = @partnerid ;

tsql