Must declare the scalar variable

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 can use proper parameterization rather than use concatenation which, if you get into that habit, you will expose yourself 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;

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

See this question and this workaround.

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.

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;

Must declare the scalar variable

Related

Recent Posts