Must declare the scalar variable sql ошибка

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.

Posted by Marta on March 17, 2023

Viewed 386 times

If you’re working with SQL Server or any other relational database management system, you may encounter an error message that says “Must declare the scalar variable” when executing a query.

This error occurs when the query references a variable that has not been declared or is out of scope.

In this article, we will discuss what this error is and how to fix this error with code examples.

What is the error “Must Declare the Scalar Variable”?

The error message “Must declare the scalar variable” is a common error in programming, particularly in SQL. This error typically occurs when a variable is used in a query or command but has not been declared or defined.

In SQL, a scalar variable is a variable that can hold a single value, such as an integer, string, or date. When using a scalar variable in a query, the variable must be declared before it can be used.

If the variable is not declared, the system will not recognize the variable, resulting in the error message “Must declare the scalar variable.”

For example, consider the following SQL query:

SELECT * FROM Customers WHERE CustomerID = @CustID

In this query, the variable @CustID is being used to retrieve a specific customer’s information from the Customers table. However, if the variable has not been declared, the system will not know what @CustID refers to, resulting in the “Must declare the scalar variable” error.

Three Steps to fix it

Declare the variable

The first step in fixing the “Must declare the scalar variable” error is to declare the variable. The variable declaration should be done before using it in the query. For example, consider the following query:

SELECT *
FROM Customers
WHERE FirstName = @FirstName

In this query, the variable @FirstName is referenced, but it has not been declared. To fix this, we need to declare the variable as follows:

DECLARE @FirstName VARCHAR(50)
SET @FirstName = 'John'

SELECT *
FROM Customers
WHERE FirstName = @FirstName

In this example, we declared the variable @FirstName as a VARCHAR with a length of 50 and assigned it the value ‘John’. Now we can use this variable in the query.

Check the variable scope

If the variable has been declared but you’re still getting the “Must declare the scalar variable” error, then it could be because the variable is out of scope. For example, consider the following code:

DECLARE @FirstName VARCHAR(50)

IF @FirstName = 'John'
BEGIN
    SELECT *
    FROM Customers
    WHERE FirstName = @FirstName
END

In this example, we declared the variable @FirstName, but it is out of scope when we reference it inside the IF block. To fix this, we need to declare the variable inside the IF block as follows:

DECLARE @FirstName VARCHAR(50)

IF @FirstName = 'John'
BEGIN
    DECLARE @FirstName VARCHAR(50)
    SET @FirstName = 'John'

    SELECT *
    FROM Customers
    WHERE FirstName = @FirstName
END

In this example, we declared the variable @FirstName inside the IF block and assigned it the value ‘John’. Now we can use this variable in the query.

Use parameterized queries

Another way to avoid the “Must declare the scalar variable” error is to use parameterized queries. Parameterized queries are a way of passing values to a query in a secure and efficient manner. For example, consider the following parameterized query:

DECLARE @FirstName VARCHAR(50) = 'John'

SELECT *
FROM Customers
WHERE FirstName = @FirstName

In this example, we declared the variable @FirstName and assigned it the value ‘John’. We then used this variable in the query. The difference between this example and the first example is that we used a parameterized query instead of a variable in the WHERE clause.

Conclusion

In conclusion, the “Must declare the scalar variable” error occurs when a query references a variable that has not been declared or is out of scope.

To fix this error, you can declare the variable, check the variable scope, or use parameterized queries. By following these steps, you can ensure that your queries execute without any errors.

I hope this article was useful, and thanks for reading and supporting this blog!

More Interesting Articles