"Must declare the scalar variable "@RowFrom"."
'Declare @Rt int'
'SET @Rt = ' + @RowTo
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 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;