'Must declare the scalar variable' error msg.

Question

'Must declare the scalar variable' error msg.

nayg

Old Hand

Points: 328
More actions
March 10, 2021 at 2:35 am

#3855767
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
```
- This topic was modified 3 years, 9 months ago by nayg.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24392 More actions · Answer 1

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;

How to post data/code on a forum to get the best help.

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 2

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.

nayg Old Hand Points: 328 More actions · Answer 3

nayg

Old Hand

Points: 328

March 10, 2021 at 11:52 pm

#3856282

Got it.

Thank you.