March 10, 2021 at 2:35 am
I have a simple query, however I am failing to understand the issue in my statement and getting below error:
SQL Statement: select @V_Cnt=count(0) from information_schema.tables
Msg 137, Level 15, State 1, Line 15
Must declare the scalar variable "@V_Cnt".
Count: 0
My Query is:
declare @V_Cntint=0;
declare @V_sqlNVARCHAR(MAX);
declare @Printnvarchar(1)='E'; -- P for Print and E for Exec
begin
set @V_sql = N'select @V_Cnt=count(0) from information_schema.tables';
print 'SQL Statement: '+@V_sql;
if (@Print = 'P') Print(@V_sql) else if(@Print = 'E') exec (@V_sql);
Print 'Count: '+cast(@V_Cnt as nvarchar);
end
March 10, 2021 at 7:27 am
Your issue is that you are using a variable inside the dynamic SQL that is declared outside the dynamic SQL. For this you will need to use sp_executesql
This change will get the results that you are looking for
DECLARE @V_Cnt int = 0;
DECLARE @V_sql nvarchar(MAX);
DECLARE @Print nvarchar(1) = N'E'; -- P for Print and E for Exec
BEGIN
SET @V_sql = N'select @V_Cnt=count(0) from information_schema.tables';
PRINT 'SQL Statement: ' + @V_sql;
IF ( @Print = 'P' )
BEGIN
PRINT ( @V_sql );
END
ELSE IF ( @Print = 'E' )
BEGIN
EXEC sys.sp_executesql @stmt = @V_sql
, @params = N'@V_Cnt int OUTPUT'
, @V_Cnt = @V_Cnt OUTPUT;
END;
PRINT 'Count: ' + CAST(@V_Cnt AS nvarchar);
END;
March 10, 2021 at 6:45 pm
To add to what DesNorton noted, local variables only exist in a certain scope of this batch. Moving to some other batch, even inside a stored procedure, puts local variables outside of scope. To make your code work, you would need to
set @V_sql = N'declare @V_Cnt int; select @V_Cnt=count(0) from information_schema.tables';
print 'SQL Statement: '+@V_sql;
However, the value from the internal V_Cnt variable inside the string from EXEC would not be in scope in the calling batch. sp_executesql sends a copy to the inner batch and then returns it back.
March 10, 2021 at 11:52 pm
Got it.
Thank you.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy