If you’re getting SQL Server error 137 that goes something like “Must declare the scalar variable…“, it’s probably because you’re referring to a variable that hasn’t been declared.

If the variable has been declared, it’s possible you’re referring to it incorrectly in the code.

When we use a variable in SQL Server, we must declare the variable first.

To fix this issue, declare the variable. Also be sure to use the right syntax when using it.

Example of Error

Here’s an example of code that produces the error:

SET @FirstName = 'Homer';

Result:

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

Here, I tried to set a value for the @FirstName variable, but I hadn’t declared it yet.

Here’s another example of where we can get the error:

SELECT @FirstName;

Result:

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

Once again, it’s because I didn’t declare the variable before trying to use it.

However, even if we do declare the variable, we may still find ourselves looking at the same error. For example, if we use the EXECUTE statement to do something like this:

DECLARE @BookTitle nvarchar(20);    
SET @BookTitle = 'Title';
EXECUTE ('SELECT @BookTitle FROM Books;');

Result:

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

We again get the dreaded 137 error. This is because, although we’ve declared the variable, we’re trying to use it from within the SELECT statement that we’re trying to execute with the EXECUTE statement. When we do it like this, the variable is local to the SELECT statement, which is actually outside of the EXECUTE statement (which is why we get the error).

Solution

To fix this issue, we need to declare the variable, and be sure that it’s referenced in the correct way.

Here’s an example of fixing the first example:

DECLARE @FirstName nvarchar(20);
SET @FirstName = 'Homer';

Result:

Commands completed successfully.

Here’s an example of fixing the second example:

DECLARE @FirstName nvarchar(20);
SELECT @FirstName;

Result:

NULL

This returned NULL because I didn’t set a value for the variable. This is to be expected. In any case, the error has gone.

And let’s fix the last one:

DECLARE @BookTitle nvarchar(20);
SET @BookTitle = 'Title';
EXECUTE ('SELECT ' + @BookTitle + ' FROM Books;');

Result:

Title          
---------------
The Great Novel
Amazing Stories
Sesh's Memoir  

This time it worked as expected.

So in this case I concatenated the @BookTitle variable with the SELECT statement. The SELECT statement will therefore be executed as:

SELECT Title FROM Books;

So this is how we were able to successfully eliminate the error.