CODEWITHSUNDEEP
sqlsql-server-2008
Bill G
Bill G

Reputation: 161

using dynamic sql in a stored procedure

I have a stored procedure that takes as a parameter the following variable that holds a table name, @percapHist. It will hold a value something like 'percaphist1408'.

I want to count the number of records in the table and save the value in a variable to be used later. I have now tried using dynamic sql. the statement compiles quickly but when run it says i must declare the scalar variable @percapcount. here is the dynamic sql statement that is being used inside the store procedure.

SET @tsql = 'SELECT @percapCount = Count(socSecNo) FROM ' + @percapHist
EXEC(@tsql)

when i examine the variable @tsql after the above statement is executed i see the following

SELECT @percapCount = Count(socSecNo) FROM percapHist1408

but when i run the above EXEC I get the 'must declare scalar variable @perapcount'

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

If you want to return a value from dynamic SQL, then use sp_executesql:

DECLARE @tsql nvarchar(max);
DECLARE @percapCount int;

SET @tsql = 'SELECT @percapCount = Count(socSecNo) FROM ' + @percapHist;

EXEC sp_executesql @tsql, N'@percapCount INT OUTPUT', @percapCount = @percapCount OUTPUT;

Upvotes: 1

Related Questions