Assign result of dynamic sql to variable

by

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

More Related Contents:

  1. Find index of last occurrence of a sub-string using T-SQL
  2. SQL to find first non-numeric character in a string
  3. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  4. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  5. How do I get list of all tables in a database using TSQL?
  6. Storing images in SQL Server?
  7. Escape a string in SQL Server so that it is safe to use in LIKE expression
  8. Limitations of SQL Server Express
  9. SQL time difference between two dates result in hh:mm:ss
  10. Storing files in SQL Server
  11. Dynamic SQL (passing table name as parameter)
  12. Dapper.NET and stored proc with multiple result sets
  13. SQL Server convert string to datetime
  14. “order by newid()” – how does it work?
  15. T-SQL Dynamic SQL and Temp Tables
  16. Efficient ISNUMERIC() replacements on SQL Server?
  17. How to determine total number of open/active connections in ms sql server 2005
  18. Login to Microsoft SQL Server Error: 18456
  19. Tablix: Repeat header rows on each page not working – Report Builder 3.0
  20. Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  21. TABLOCK vs TABLOCKX
  22. Replace single quotes in SQL Server
  23. Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?
  24. sql use statement with variable
  25. How to create a yes/no boolean field in SQL server?
  26. SQL Server Passthrough query as basis for a DAO recordset in Access
  27. T-SQL query to show table definition?
  28. Copy table to a different database on a different SQL Server
  29. Include missing months in Group By query
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment