sql server 2008 r2 - How Use "WITH in subquery" (MS SQL) -

sql server 2008 r2 - How Use "WITH in subquery" (MS SQL) -

declare @str varchar(max) ,@s varchar(max) ,@s2 varchar(max) ,@s3 varchar(50) set @str = '' set @s2 = ( ;with names(name1) (select * dbo.test ... ) select @str=@str+isnull('<onerow car="'+name1+'"/> ','') names select @str xmltext ) update dbo.test2 set = 1 b=@s2 ... wrong syntax near ';'!

as mentioned in comment using variable in select statement not deterministic, instead utilize sql server's xml extensions, reliable:

you need utilize select @s2 = instead of set if wish utilize mutual table expression:

declare @s2 varchar(max); names(name1) ( select name dbo.test ... ) select @s2 = ( select isnull('<onerow car="'+name1+'"/> ','') names xml path(''), type ).value('.', 'varchar(max)'); update dbo.test2 set = 1 b=@s2;

sql-server-2008-r2 subquery with-statement