Must declare a scalar variable

Question

@RowFrom int

@RowTo int

are global input pairs for the stored procedure, and since I compile the SQL query inside the stored procedure using T-SQL, using Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try use @RowFrom or @RowTo inside the @RowTo variable that is running .. it works fine otherwise .. please help.

 "Must declare the scalar variable "@RowFrom"." 

---

In addition, I tried to include the following in the @sqlstatement variable:

 'Declare @Rt int' 'SET @Rt = ' + @RowTo 

but @RowTo still does not pass its @Rt value and generates an error.

Answer 1

You cannot concatenate int into string. Instead:

 SET @sql = N'DECLARE @Rt INT; SET @Rt = ' + @RowTo; 

You need:

 SET @sql = N'DECLARE @Rt INT; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo); 

To illustrate what is 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 sp_executeSQL @sql; 

To build this into a string (even if it ends up being a number), I need to convert it. But, as you can see, the number is still treated as a number when it is executed. Answer: 25, right?

In your case, you do not need to re-declare @Rt, etc. inside the @sql line, you just need to say:

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

Although it would be better to have the correct parameterization, for example

 SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;'; EXEC sp_executesql @sql, N'@RowFrom INT, @RowTo INT', @RowFrom, @RowTo; 

Answer 2

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

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

if you, for example, seal S in

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

Sorry to unscrew the answers already posted here, but this is an actual instance of the error reported.

Please also note that the error does not display the value of capital S in the message, I am not sure why, but I think it is because

 Set @sql = 

located to the left of the equal sign.

Answer 3

Just adding that it fixed it for me, where a spelling error is suspected like this MSDN blog ...

When splitting SQL strings into several lines, make sure that you are a comma separating your SQL string from your parameters (and do not try to concatenate them!) And do not skip spaces at the end of each split line. Not rocket science, but I hope that I will save someone from 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; 

Answer 4

You may also receive this error message if a variable is declared before GO and specified after it.

See this question and this workaround .

Answer 5

Case sensitivity will also cause this problem.

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

Answer 6

Just an answer for the future of me (maybe this will help someone else!). 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 will get the error:

 Must declare the scalar variable "@year" 

This is because you are trying to run a bunch of code that includes BOTH executing the save procedure AND request under it (!). Just highlight the one you want to run, or delete / comment out the one you are not interested in.