Must declare the scalar variable – Error Message 137

This article lists out the extensive list of scenarios in which we get the following error message and how to resolve it.

Error Message:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “%.*ls”.

Root Cause:

This error occurs if we are trying to use an undeclared variable

Below are the couple of scenarios in which we come across this error and how to resolve it.

Scenario 1: Trying to use an undeclared variable

Try executing the below statement

PRINT @AuthorName

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the PRINT statement without declaring it, which is not allowed by Sql Server.

Solution:Declare the @AuthorName variable before using it in the PRINT statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName

RESULT:
Error Message 128

Scenario 2: Trying to use a local declared variable after batch separator GO statement

Try executing the below statement

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName
GO
PRINT @AuthorName

RESULT:

Basavaraj Biradar
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the PRINT statement after the batch separator GO Statement. Basically the scope of the local variables is within the batch in which it is declared.

Solution:Re-declare the @AuthorName variable before using it in the PRINT statement after the GO statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName
GO
DECLARE @AuthorName VARCHAR(100) = 'Basava'
PRINT @AuthorName

RESULT:
Error Message 137

Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement

Try executing the below statement

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE ('PRINT @AuthorName')

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the statement executed by EXECUTE statement. EXECUTE statement doesn’t have the visibility of the variables declared outside of it.

Solution: We can rewrite the above statements as below to resolve this issue:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE ('PRINT ''' + @AuthorName + '''' )

RESULT:
Must declare the scalar variable

Alternative solution: One more alternative solution for the above problem, is to use the SP_EXECUTESQL statement which allows parameterized statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE SP_EXECUTESQL N'PRINT @AuthorName',
           N'@AuthorName VARCHAR(100)',@AuthorName

RESULT:
Must declare the scalar variable SP_EXECUTESQL

Let me know if you have encountered this error in any other scenario.

8 thoughts on “Must declare the scalar variable – Error Message 137

  1. This approach of teaching errors is very good
    Please keep publishing some more such articles

    I enjoyed reading this article and recommend others

    Thanks for educating the community

    1. HI Kris

      could you please help with this
      getting the same error “Declare a scalar variable”
      CREATE PROCEDURE [dbo].[GetWarranties]
      @languageID varchar(10), @defaultLanguageID varchar(10), @warrantyID int= NULL

      –suppose lots of parameters

      AS
      BEGIN

      DECLARE @WarrantyCount varchar(50)

      Set @WarrantyCount = (Select Count(warrantyText)
      From GlobalCustomerSaysTest.dbo.warranty
      where languageid = @languageid
      AND warrantyactive = 1)

      If @WarrantyCount = 0
      Set @defaultLanguageID = (Select Count(warrantyText)
      From GlobalCustomerSaysTest.dbo.warranty
      where languageid = @dafaultLanguageID
      AND warrantyActive = 1)

      ELSE
      Set @WarrantyCount = (Select Count(warrantyText)
      From GlobalCustomerSaysTest.dbo.warranty
      Where warrantyid = ISNULL(@warrantyID, warrantyid))

      END
      GO

      1. Thanks Basavaraj.

        Your diagnostic of “GO Statement in between statements” is spot on – solved my issue.
        Great help to those who write t-sql scripts once in a blue moon.

        1. Thank you Fabian Lee. I happy that this article helped you… Such comments keeps me motivated to write articles…

  2. Hi,

    Thank you for the post its very helpful. Actually I have a peculiar issue started from 3 weeks back after migrating to the new server.

    Trying to run below command
    declare @MinPartitionID int = 12
    ALTER TABLE dbo.FACT SWITCH PARTITION @MinPartitionID
    TO dbo.FACT_SWITCH PARTITION @MinPartitionID

    Error:
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable “@MinPartitionID”.
    Msg 3609, Level 16, State 2, Line 2
    The transaction ended in the trigger. The batch has been aborted.

    Tried manipulating it based on your scenario 2 and it worked:
    declare @MinPartitionID int = 12
    EXECUTE (‘ALTER TABLE dbo.FACT SWITCH PARTITION ‘+ @MinPartitionID +
    ‘TO dbo.FACT_SWITCH PARTITION ‘ + @MinPartitionID )

    Can you please help with the error, since may of the job uses this statement and currently doing things manually due to the error.

Leave a Reply to Bb Cancel reply

Your email address will not be published.