TSQL - how to execute a query as a variable?
77,706
Solution 1
You can use sp_executesql
with an output
parameter to retrieve the scalar result.
DECLARE @query as nvarchar(200), @count int;
SET @query = N'SELECT @count = COUNT(*) FROM table';
EXEC sp_executesql @query,
N'@count int OUTPUT',
@count = @count OUTPUT
SELECT @count AS [@count]
Solution 2
You can do it like this:
exec (@query)
or, preferably, like this:
execute sp_executesql @query
For more info, check this MSDN article.
Author by
Malyo
Updated on July 05, 2022Comments
-
Malyo about 2 years
DECLARE @query as varchar(200); SET @query = 'SELECT COUNT(*) FROM table';
How can I execute
@query
, and additionally, is there way to store the query result directly when assigning the variable? -
Malyo about 12 yearsThanks, not sure if i understand the code propertly, does it asign query result into count variable, by OUTPUT?
-
Martin Smith about 12 years@Malyo - It declares a variable
@count
in the outer scope. This gets passed as an output parameter in thesp_executesql
call which means the assignment to the output parameter is propagated to that variable. See TSQL Basics II - Parameter Passing Semantics for more aboutOUTPUT
parameters.