Assign result of dynamic sql to variable
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
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 INT
INSERT @CountResults
EXEC(@SqlStatement)
SET @Count = (SELECT CountReturned FROM @CountResults)
SELECT @Count