using dynamic sql in a stored procedure

Question

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'

you have to declare variable before use, say declare @perapcount int — HaveNoDisplayName
– HaveNoDisplayName, Commented Nov 30, 2014 at 21:46

Gordon Linoff · Accepted Answer · 2014-11-30 21:47:15Z

1

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;

answered Nov 30, 2014 at 21:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill G Over a year ago

i tried the above exactly as you have it. it compiles ok but when i run it by stepping through the lines of code the highlighted line rests on the EXEC statement and part of the SELECT statement which follows. For some reason it doesnt realize the line statement ends at the ; after output.

Gordon Linoff Over a year ago

@BillG . . . That is strange behavior. The semicolon is standard in SQL Server for ending statements.

Collectives™ on Stack Overflow

using dynamic sql in a stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related