'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 4 years, 3 months ago by nayg.

• DesNorton

  SSC-Insane

  Points: 24517

  More actions

  March 10, 2021 at 7:27 am

  #3855838

  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: 738277

  More actions

  March 10, 2021 at 6:45 pm

  #3856143

  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

  March 10, 2021 at 11:52 pm

  #3856282

  Got it.

  Thank you.