CODEWITHSUNDEEP
sqlsql-server
Nitrodbz
Nitrodbz

Reputation: 1286

Get SQL result into variable

Here is what I have so far. I want to store the query result into an int variable but I get null so far. After execute, both @query and @countrow are both null. Any suggestion would be great.

    SET @query = N'select @countrow=count(*) from ' + @tablename 
EXECUTE sp_executesql @query

Upvotes: 3

Views: 465

Answers (2)

Gidil
Gidil

Reputation: 4137

DECLARE @i INT, @sql NVARCHAR(512), @tablename varchar(200) = 'tbl'

SET @sql = N'SELECT @i = COUNT(*) FROM '  + @tablename

EXEC sp_executesql 
    @query = @sql, 
    @params = N'@i INT OUTPUT', 
    @i = @i OUTPUT 

PRINT @i

Take a look at SQL Fiddle

Upvotes: 3

Taryn
Taryn

Reputation: 247860

You need to use the OUTPUT keyword, similar to this:

declare @query nvarchar(max)
declare  @countrow int
declare  @tablename varchar(50)

SET @query = N'select @cnt=count(*) from ' + @tablename 
EXECUTE sp_executesql @query, N'@cnt int OUTPUT', @cnt=@countrow OUTPUT

select @countrow as countrow -- to get the result

Upvotes: 2

Related Questions