You can’t concatenate an int to a string. Instead of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
You need:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
To help illustrate what’s happening here. Let’s say @RowTo = 5.
DECLARE @RowTo int;
SET @RowTo = 5;
DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;
In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it’s executed. The answer is 25, right?
In modern versions you can use CONCAT() to avoid handling NULL or conversion issues:
SET @sql = CONCAT(N'SELECT ', @RowTo, ' * 5');
But in your case you should use proper parameterization rather than concatenation. If you keep using concatenation, you will expose yourself to SQL injection at some point (see this and this):
SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
EXEC sys.sp_executesql @sql,
N'@RowFrom int, @RowTo int',
@RowFrom, @RowTo;
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:
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:
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:
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:
Let me know if you have encountered this error in any other scenario.
Trying to update two tables (City and Location) using two table variables (@NameZip, @NameZip2). The city names have ZipCodes and ZipCodes have Names instead of vice versa. Updates are changing City names and ZipCodes where they were wrongly entered. But getting the error:
Msg 137, Level 16, State 1, Line 28
Must declare the scalar variable «@NameZip2».Msg 137, Level 16, State 1, Line 32
Must declare the scalar variable «@NameZip».
The query I wrote:
--first table variable
DECLARE @NameZip TABLE
(
Zip_Code NVARCHAR(100),
Name NVARCHAR(100),
id_city INT
)
--second table variable
DECLARE @NameZip2 TABLE
(
Zip_Code nvarchar(100),
Name NVARCHAR(100),
id_city INT
)
--inserting into first table variable from City and Location table
INSERT INTO @NameZip (Zip_code, Name, id_city)
SELECT B.Zip_Code, A.Name, A.id_city
FROM City A
INNER JOIN Location B ON A.id_city = B.id_city
AND Name NOT LIKE '%[^0-9]%'
--inserting into second table variable from first table variable
INSERT INTO @NameZip2(Zip_code, Name, id_city)
SELECT Name, Zip_Code, id_city
FROM @NameZip
UPDATE City
SET Name = (SELECT Name FROM @NameZip2)
WHERE City.id_city = @NameZip2.id_city -- I get error on this line
UPDATE Location
SET Zip_Code = (SELECT Zip_Code FROM @NameZip2)
WHERE Zip_Code = @NameZip.Zip_Code -- I get error on this line
Any inputs regarding this would be appreciated.
|
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>». |
||
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 : | ||
|
The ‘Must Declare the Scalar Variable’ error often occurs in SQL Server when you try to use a variable without declaring it first. In this guide, we will discuss step-by-step solutions and tips on how to fix this error and avoid it in the future.
Table of Contents
- Understanding the ‘Must Declare the Scalar Variable’ Error
- Step-by-Step Solutions
- Solution 1: Declare the Variable
- Solution 2: Check Variable Spelling
- Solution 3: Maintain Variable Scope
- Tips to Avoid the Error
- FAQs
Before we dive into the solutions, let’s take a moment to understand the error. The ‘Must Declare the Scalar Variable’ error occurs when SQL Server encounters a variable that has not been declared. In SQL Server, you must declare a variable before using it in any expression or statement.
Here is an example of code that triggers the error:
BEGIN
SET @FirstName = 'John'
SELECT @FirstName
END
In this example, we are trying to set the value of the @FirstName variable without declaring it first. As a result, we will get the error message:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@FirstName".
Now, let’s look at the step-by-step solutions to fix this error.
Step-by-Step Solutions
Solution 1: Declare the Variable
The most straightforward solution is to declare the variable before using it. You can declare a variable using the DECLARE statement. Here’s the corrected version of the previous example:
DECLARE @FirstName NVARCHAR(50)
BEGIN
SET @FirstName = 'John'
SELECT @FirstName
END
By declaring the variable @FirstName before using it, we have resolved the error.
Solution 2: Check Variable Spelling
Sometimes, the error may be caused by a simple typo in the variable name. Make sure that you have spelled the variable name correctly and consistently throughout your code. For instance, if you have declared your variable as @FirstName but use @FistName at some point in your code, you will encounter the error.
Solution 3: Maintain Variable Scope
Another common cause of the error is the variable scope. In SQL Server, a variable declared within a block is only available within that block. If you try to use the variable outside of the block where it was declared, you will receive the ‘Must Declare the Scalar Variable’ error.
To fix this issue, make sure that you declare and use the variable within the same scope. For example:
DECLARE @FirstName NVARCHAR(50)
BEGIN
SET @FirstName = 'John'
SELECT @FirstName
END
In this case, the variable @FirstName is declared and used within the same block, so the error will not occur.
Tips to Avoid the Error
Here are some tips to help you avoid the ‘Must Declare the Scalar Variable’ error:
- Always declare your variables before using them.
- Use meaningful and consistent variable names to prevent typos.
- Make sure you are aware of the variable scope and declare your variables within the appropriate scope.
FAQs
Q: What is a scalar variable in SQL Server?
A: A scalar variable in SQL Server is a variable that holds a single value, as opposed to a table or collection of values. Scalar variables are commonly used to store intermediate results, parameters, or control flow information in your SQL scripts.
Q: Can I declare and set a variable in the same statement?
A: Yes, you can declare and set a variable in the same statement using the DECLARE statement with the = operator. For example:
DECLARE @FirstName NVARCHAR(50) = 'John'
Q: Can I declare multiple variables in a single statement?
A: Yes, you can declare multiple variables in a single statement by separating them with commas. For example:
DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50)
Q: How do I determine the appropriate data type for my variables?
A: Choose the appropriate data type based on the kind of data you will store in the variable. For instance, if you need to store a name, use the NVARCHAR data type, while for numeric values, you can use INT, DECIMAL, or other numeric data types.
Q: Can I use a variable in a stored procedure?
A: Yes, you can use variables in stored procedures. Just make sure to declare the variables within the stored procedure before using them.
Learn more about variables in SQL Server