bill bill - 27 days ago 11
SQL Question

Must declare the scalar variable

@RowFrom int


@RowTo int


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
@RowFrom
or
@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
@sqlstatement
variable:

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


but
@RowTo
still doesn't pass its value to
@Rt
and generates an error.

Answer

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 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 sp_executesql @sql,
  N'@RowFrom INT, @RowTo INT',
  @RowFrom, @RowTo;
Comments