When I try to utilize the @RowFrom or @RowTo inside the @sqlstatement variable that is executed, it gives me this issue because I am compiling the SQL query inside the Stored Procedure with T-SQL and then using Exec(@sqlstatement) at the conclusion of the stored procedure to present the result. Otherwise, everything is ok… pls assist.
"Must declare the scalar variable "@RowFrom"."
I also tried using the @sqlstatement variable to include the following:
'Declare @Rt int' 'SET @Rt = ' + @RowTo
However, @RowTo fails to deliver its value to @Rt, resulting in an error.
Asked by bill
An int cannot be concatenated with a string. in place of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);
To help visualize what’s going on here. Assume @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;
I need to convert that to a string (even if it will eventually be a number) before I can create it. However, as you can see, when the number is executed, it is still treated as a number. Isn’t it true that the answer is 25?
You can use appropriate parameterization instead of concatenation in your case, which, if you get into the habit of doing so, will expose you 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;
Answered by Aaron Bertrand
You can also get this error message if a variable is declared before a GOand referenced after it.
See this question for more information, as well as this workaround.
Answered by Pierre C
Just a heads up, I know this is an old article, but you can receive this issue on a statement like this depending on the database COLLATION settings.
SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';
If you, for example, misspell the letter S in the word
SET @sql = @***S***ql
Sorry for rehashing what has previously been said, but this is a real-life example of the problem.
It’s also worth noting that the error notice will not include a capital S; I’m not sure why, but I believe it’s because the
Set @sql =
is on the opposite side of the equal sign.
Answered by htm11h
Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog…
If you’re splitting SQL strings across many lines, make sure you’re separating your SQL string from your parameters with a comma (rather than trying to concatenate them!) Then making sure there are no gaps at the end of each split line It’s not rocket science, but I’m hoping to save someone some time and aggravation.
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;
Answered by Tim Tyler
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.
The usage of ; following a SQL statement created this problem in my case. Simply delete it, and the mistake will be resolved.
I assume the cause is the same as @IronSean stated in a previous comment:
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
Answered by ViRuSTriNiTy
Post is based on https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable