Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Last Day of the Month Function (EOMONTH)
Error Messages
Home > SQL Server Error Messages > Msg 137 - Must declare the scalar variable "<Variable Name>".
SQL Server Error Messages - Msg 137 - Must declare the scalar variable "<Variable Name>".

Error Message

Server: Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "<Variable Name>".

Causes

As the error message suggests, a local variable is being used within a script, stored procedure or function that is not declared using the DECLARE statement.

To illustrate, the simplest way to generate this error is as follows:

SET @FirstName = 'Mickey'

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@FirstName".
SELECT @HighestScore

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@HighestScore".

A not-so-obvious way of getting this error message is as follows:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END

GO

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DateFormat".

This script tries to print the current date into the different date formats between date formats 0 to 14 followed by date formats 100 to 114. Although the @DateFormat local variable is declared at the beginning of the script, there is a GO command just before the group of statements that prints the current date into the different date formats from 100 to 114. The GO command signals the end of a batch of Transact-SQL statements. A local variable is only valid within the body of a batch or procedure. Since there is a GO command, the @DateFormat local variable will not exist anymore on the second batch of commands.

Yet another way of getting this error is when using a local variable declared outside a dynamic SQL statement executed using the EXECUTE statement. To illustrate:

DECLARE @ColumnName		VARCHAR(100)
SET @ColumnName = 'FirstName'

EXECUTE ('SELECT [CustomerID], @ColumnName FROM [dbo].[Customers]')
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ColumnName".

This script tries to return the row values for a given column as defined in the @ColumnName local variable from a given table.

Solution / Workaround:

As the message suggests, this error can easily be avoided by making sure that a local variable is defined first using the DECLARE statement before being used.

In the first case described above, simply declare the local variables just before setting its value or returning its value as part of a SELECT statement:

DECLARE @FirstName	VARCHAR(50)
SET @FirstName = 'Mickey'
GO

DECLARE @HighestScore	INT
SELECT @HighestScore
GO

As for the second scenario, there are 2 ways of avoiding the error.  The first option is to remove the GO command between the 2 sets of scripts so that the local variable @DateFormat is valid and accessible on both scripts:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

The second option is to define the local variable @DateFormat again just after the GO command and just before it gets initialized and used:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

DECLARE @DateFormat INT

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

And lastly for the third scenario, the query needs to be re-written such that the value of the local variable is used in the dynamic statement instead of the local variable.

DECLARE @ColumnName		VARCHAR(100)
SET @ColumnName = 'FirstName'

EXECUTE ('SELECT ' + @ColumnName + ' FROM [dbo].[Customers]')
Related Articles :