[sql-server] Must declare the scalar variable

I wrote this SQL in a stored procedure but not working,

declare @tableName varchar(max) = 'TblTest'
declare @col1Name varchar(max) = 'VALUE1'
declare @col2Name varchar(max) = 'VALUE2'
declare @value1 varchar(max)
declare @value2 varchar(200)

execute('Select TOP 1 @value1='+@col1Name+', @value2='+@col2Name+' From '+ @tableName +' Where ID = 61')

select @value1

execute('Select TOP 1 @value1=VALUE1, @value2=VALUE2 From TblTest Where ID = 61')

This SQL throws this error:

Must declare the scalar variable "@value1".

I am generating the SQL dynamically and I want to get value in a variable. What should I do?

This question is related to sql-server tsql stored-procedures executequery

The answer is


Here is a simple example :

Create or alter PROCEDURE getPersonCountByLastName (
@lastName varchar(20),
@count int OUTPUT
)
As
Begin
select @count = count(personSid) from Person where lastName like @lastName
End;

Execute below statements in one batch (by selecting all)

1. Declare @count int
2. Exec getPersonCountByLastName kumar, @count output
3. Select @count

When i tried to execute statements 1,2,3 individually, I had the same error. But when executed them all at one time, it worked fine.

The reason is that SQL executes declare, exec statements in different sessions.

Open to further corrections.


This will occur in SQL Server as well if you don't run all of the statements at once. If you are highlighting a set of statements and executing the following:

DECLARE @LoopVar INT
SET @LoopVar = (SELECT COUNT(*) FROM SomeTable)

And then try to highlight another set of statements such as:

PRINT 'LoopVar is: ' + CONVERT(NVARCHAR(255), @LoopVar)

You will receive this error.


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 your case you don't really need to re-declare @Rt etc. inside the @sql string, you just need to say:

SET @sql = @sql + ' WHERE RowNum BETWEEN ' 
    + CONVERT(varchar(12), @RowFrom) + ' AND ' 
    + CONVERT(varchar(12), @RowTo);

Though it would be better to have proper parameterization, e.g.

SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';

EXEC sys.sp_executesql @sql,
  N'@RowFrom int, @RowTo int',
  @RowFrom, @RowTo;

Just an answer for future me (maybe it helps someone else too!). If you try to run something like this in the query editor:

USE [Dbo]
GO

DECLARE @RC int

EXECUTE @RC = [dbo].[SomeStoredProcedure] 
   2018
  ,0
  ,'arg3'
GO

SELECT month, SUM(weight) AS weight, SUM(amount) AS amount 
FROM SomeTable AS e 
WHERE year = @year AND type = 'M'

And you get the error:

Must declare the scalar variable "@year"

That's because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.


You can also get this error message if a variable is declared before a GOand referenced after it.

See this question and this workaround.


If someone else comes across this question while no solution here made my sql file working, here's what my mistake was:

I have been exporting the contents of my database via the 'Generate Script' command of Microsofts' Server Management Studio and then doing some operations afterwards while inserting the generated data in another instance.

Due to the generated export, there have been a bunch of "GO" statements in the sql file.

What I didn't know was that variables declared at the top of a file aren't accessible as far as a GO statement is executed. Therefore I had to remove the GO statements in my sql file and the error "Must declare the scalar variable xy" was gone!


Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog...

When splitting SQL strings over multiple lines, check that that you are comma separating your SQL string from your parameters (and not trying to concatenate them!) and not missing any spaces at the end of each split line. Not rocket science but hope I save someone a headache.

For example:

db.TableName.SqlQuery(
    "SELECT Id, Timestamp, User " +
    "FROM dbo.TableName " +
    "WHERE Timestamp >= @from " +
    "AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!]
    new SqlParameter("from", from),
    new SqlParameter("till", till)),
    .ToListAsync()
    .Result;

Just FYI, I know this is an old post, but depending on the database COLLATION settings you can get this error on a statement like this,

SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';

if for example you typo the S in the

SET @sql = @***S***ql 

sorry to spin off the answers already posted here, but this is an actual instance of the error reported.

Note also that the error will not display the capital S in the message, I am not sure why, but I think it is because the

Set @sql =

is on the left of the equal sign.


This is most likely not an answer to the issue itself but this question pops up as first result when searching for Sql declare scalar variable hence i share a possible solution to this error.

In my case this error was caused by the use of ; after a SQL statement. Just remove it and the error will be gone.

I guess the cause is the same as @IronSean already posted in an comment above:

it's worth noting that using GO (or in this case ;) causes a new branch where declared variables aren't visible past the statement.

For example:

DECLARE @id int
SET @id = 78

SELECT * FROM MyTable WHERE Id = @var; <-- remove this character to avoid the error message
SELECT * FROM AnotherTable WHERE MyTableId = @var

Case Sensitivity will cause this problem, too.

@MyVariable and @myvariable are the same variables in SQL Server Man. Studio and will work. However, these variables will result in a "Must declare the scalar variable "@MyVariable" in Visual Studio (C#) due to case-sensitivity differences.


-- CREATE OR ALTER PROCEDURE

ALTER PROCEDURE out (

@age INT,
@salary INT OUTPUT)

AS BEGIN

SELECT @salary = (SELECT SALARY FROM new_testing where AGE = @age  ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

END

-----------------DECLARE THE OUTPUT VARIABLE---------------------------------

DECLARE @test INT

---------------------THEN EXECUTE THE QUERY---------------------------------

EXECUTE out 25 , @salary = @test OUTPUT

print @test

-------------------same output obtain without procedure------------------------------------------- SELECT * FROM new_testing where AGE = 25 ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY


Declare @v1 varchar(max), @v2 varchar(200);

Declare @sql nvarchar(max);

Set @sql = N'SELECT @v1 = value1, @v2 = value2
FROM dbo.TblTest -- always use schema
WHERE ID = 61;';

EXEC sp_executesql @sql,
  N'@v1 varchar(max) output, @v2 varchar(200) output',
  @v1 output, @v2 output;

You should also pass your input, like wherever 61 comes from, as proper parameters (but you won't be able to pass table and column names that way).


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table

Examples related to executequery

Must declare the scalar variable