sql server - Assign result of dynamic sql to variable

Question

I'm doing dynamic SQL to convert all columns in a table a string

so After after all I do

EXEC(@template); 

where @template is the dynamic generated query so:

col1  col2 col3
---------------
1    7    13 
2    8    14
3    9    15
4   10    16
5   11    17
6   12    18

(this results: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)

How do I assign to a variable the resulting string

something like?

DECLARE  @result AS varchar(max);
 SET @result = EXEC(@template); 

Answer 1

You can use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

Result:

(No column name)
1

Edit

In my sample @S is instead of your @template. As you can see I assign a value to @x so you need to modify @template so it internally assigns the comma separated string to the variable you define in your second argument to sp_executesql. In my sample N'@x int out'. You probably want a varchar(max) output parameter. Something like N'@Result varchar(max) out'

Here is another example building a comma separated string from master..spt_values

declare @template nvarchar(max)
set @template = 
'select @Result += cast(number as varchar(10))+'',''
from master..spt_values
where type = ''P''
'

declare @CommaString varchar(max)
set @CommaString = ''

exec sp_executesql @template, N'@Result varchar(max) out', @CommaString out

select @CommaString