Assign result of dynamic sql to variable

You can use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'declare @xx intset @xx = 0exec sp_executesql @S, N'@x int out', @xx outselect @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_valueswhere type = ''P'''declare @CommaString varchar(max)set @CommaString = ''exec sp_executesql @template, N'@Result varchar(max) out', @CommaString outselect @CommaString

Most of these answers use sp_executesql as the solution to this problem. I have found that there are some limitations when using sp_executesql, which I will not go into, but I wanted to offer an alternative using EXEC(). I am using SQL Server 2008 and I know that some of the objects I am using in this script are not available in earlier versions of SQL Server so be wary.

DECLARE @CountResults TABLE (CountReturned INT)DECLARE     @SqlStatement VARCHAR(8000) = 'SELECT COUNT(*) FROM table'    , @Count INTINSERT @CountResultsEXEC(@SqlStatement)SET @Count = (SELECT CountReturned FROM @CountResults)SELECT @Count

You could use sp_executesql instead of exec. That allows you to specify an output parameter.

declare @out_var varchar(max);execute sp_executesql     N'select @out_var = ''hello world''',     N'@out_var varchar(max) OUTPUT',     @out_var = @out_var output;select @out_var;

This prints "hello world".