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