are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using
Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try to use the
@RowTo inside the
@sqlstatement variable that is executed.. it works fine otherwise.. please help.
"Must declare the scalar variable "@RowFrom"."
Also, I tried including the following in the
'Declare @Rt int' 'SET @Rt = ' + @RowTo
@RowTo still doesn’t pass its value to
@Rt and generates an error.
You can’t concatenate an int to a string. Instead of:
SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;
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;
You can also get this error message if a variable is declared before a
GOand referenced after it.
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.
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.
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;
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.
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.
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!
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.
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