Get SQL result into variable

Question

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

If @query is null, then @tablename must be null

podiluska
– podiluska

2012-10-10 14:44:41 +00:00
Commented Oct 10, 2012 at 14:44 — podiluska
– podiluska, Commented Oct 10, 2012 at 14:44

Gidil · Accepted Answer · 2012-10-10 14:16:51Z

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

Taryn · Accepted Answer · 2012-10-10 14:16:27Z

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

Collectives™ on Stack Overflow

Get SQL result into variable

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related