CODEWITHSUNDEEP
t-sqlvariables
Malyo
Malyo

Reputation: 2000

TSQL - how to execute a query as a variable?

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?

Upvotes: 22

Views: 88129

Answers (3)

JSam
JSam

Reputation: 1

Try this :
declare @query as varchar(200) select @query = COUNT(*) from table

Upvotes: -2

Martin Smith
Martin Smith

Reputation: 452947

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]

Upvotes: 33

aF.
aF.

Reputation: 66687

You can do it like this:

exec (@query)

or, preferably, like this:

execute sp_executesql @query

For more info, check this MSDN article.

Upvotes: 14

Related Questions