The scalar variable must be declared.

Problem

@RowFrom int

@RowTo int

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

Solution #1

An int cannot be concatenated with a string. in place 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 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

Solution #2

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

Solution #3

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

Solution #4

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.

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;

Answered by Tim Tyler

Solution #5

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:

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

Answered by ViRuSTriNiTy

Post is based on https://stackoverflow.com/questions/7181976/must-declare-the-scalar-variable