sql-server - 在 SQL Server 2008 中将 varchar 值转换为数据类型 int 时转换失败

Question

我不知道如何解决此错误:

Conversion failed when converting the varchar value
'SELECT LastName,FirstName FROM ##Results WHERE ##RowNum BETWEEN(' to data type int.

查询是:

SET @s_query = 'SELECT ' + @ColNames1 + ' FROM ##Results
WHERE ##RowNum BETWEEN('+@PageIndex1+'-1) * '+@PageSize1+' + 1 
AND((('+@PageIndex1+' -1) * '+@PageSize1+' + 1) + '+@PageSize1+') - 1';

Answer 1

DECLARE @s_query   NVARCHAR(MAX);
DECLARE @ColNames1 NVARCHAR(MAX) = 'Some_Column'
DECLARE @PageIndex1 INT = 10;
DECLARE @PageSize1  INT = 20;

SET @s_query =  N'SELECT ' + QUOTENAME(@ColNames1) + N' FROM ##Results
                WHERE RowNum BETWEEN('+CAST(@PageIndex1 AS NVARCHAR)+ N'-1) * '
                + CAST(@PageSize1 AS NVARCHAR)+ N' + 1 AND((('+ CAST(@PageIndex1 AS NVARCHAR)+ N' -1) * '
                + CAST(@PageSize1  AS NVARCHAR)+ N' + 1) + '+ CAST(@PageSize1 AS NVARCHAR)+ N') - 1';

PRINT @s_query

SELECT [Some_Column] FROM ##Results
WHERE RowNum BETWEEN(10-1) * 20 + 1 AND(((10 -1) * 20 + 1) + 20) - 1