Tag Archives: Msg 137 Level 15 State 1 Line 1

Must declare the scalar variable – Error Message 137

Errors, Sql Server, , , , , , , , , ,

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.