|
|
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]')
|