Tag Archives: Error Message

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.

Msg 128, Level 15, State 1, Line 1 The name “%.*s” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

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

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

Error Message:

Msg 128, Level 15, State 1, Line 1
The name “%.*s” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Below are the couple of scenarios in which we come across this error and solution for it

Scenario 1: PRINT statement having table name or column name as it’s parameter

Try executing the below statement

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

RESULT:

Msg 128, Level 15, State 1, Line 2
The name “AuthorName” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Reason for this error

In the above example, the variable AuthorName name is missing @ prefix symbol in the PRINT statement and resulting in this error message. Because of the missing @ prefix, Sql Server is interpreting it as Table Column name and which is not allowed in the PRINT statement.

Solution:
Let us correct the AuthorName variable’s missing @ prefix symbol in the PRINT statement and verify the result 

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

RESULT:
Error Message 128

Scenario 2: DEFAULT value defined for the column is referring other columns of the table

Try executing the below create table statement

CREATE TABLE dbo.Orders (OrderId INT,
 Amount MONEY,  Tax    MONEY, Discount MONEY,
 Total MONEY DEFAULT (Amount + Tax + Discount))

RESULT:

Msg 128, Level 15, State 1, Line 3
The name “Amount” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Reason for this error

The reason for this error is we are trying to use tables other columns as default value for a column which is not supported. Default value for the column can only be either a constant value or a scalar function or a NULL value.

Solution: One solution to this problem is, we can create Total as a computed column like below:

CREATE TABLE dbo.Orders (OrderId INT,
 Amount MONEY,  Tax    MONEY, Discount MONEY,
 Total AS (Amount + Tax + Discount))

RESULT:
Computed Column

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

ERROR: ‘INT’ is not a recognized CURSOR option -Sql Server

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

In this article will demonstrate when we get an error like below and how to resolve it.

Msg 155, Level 15, State 2, Line 1
‘INT’ is not a recognized CURSOR option.

WHEN WE GET THIS ERROR:

We get an error of this kind when we miss prefixing @ symbol to the variable name during declaration.

Below examples reproduce this error:

EXAMPLE 1:

DECLARE Var1 INT

RESULT:

Msg 155, Level 15, State 2, Line 1
‘INT’ is not a recognized CURSOR option.

EXAMPLE 2:

DECLARE Var2 VARCHAR(50)

Msg 155, Level 15, State 2, Line 1
‘VARCHAR’ is not a recognized CURSOR option.

HOW TO SOLVE IT?

To solve this prefix the variable by the @ symbol in variable declaration statement.

So let us see the result of the above two examples if we prefix the variable name by @ symbol in the variable declaration.

EXAMPLE 1:

DECLARE @Var1 INT

RESULT:

Command(s) completed successfully.

EXAMPLE 2:

DECLARE @Var2 VARCHAR(50)

Command(s) completed successfully.