20
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?

Greenonline
  • 1,330
  • 8
  • 23
  • 31
Malyo
  • 1,990
  • 7
  • 28
  • 51

3 Answers3

31

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]
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thanks, not sure if i understand the code propertly, does it asign query result into count variable, by OUTPUT? – Malyo May 28 '12 at 08:59
  • 1
    @Malyo - It declares a variable `@count` in the outer scope. This gets passed as an output parameter in the `sp_executesql` call which means the assignment to the output parameter is propagated to that variable. See [TSQL Basics II - Parameter Passing Semantics](http://blogs.msdn.com/b/sqlprogrammability/archive/2006/06/09/624531.aspx) for more about `OUTPUT` parameters. – Martin Smith May 28 '12 at 09:02
13

You can do it like this:

exec (@query)

or, preferably, like this:

execute sp_executesql @query

For more info, check this MSDN article.

aF.
  • 64,980
  • 43
  • 135
  • 198
-2

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

JSam
  • 1