在SQL Server 2008中将varchar值转换为datatype int时转换失败

I do not know how to resolve this error:

我不知道如何解决这个错误：

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

转换varchar值时转换失败'SELECT LastName，FirstName FROM ## Results WHERE ## RowNum BETWEEN（'to data type int。

Query is:

查询是：

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

2 个解决方案

#1

You're building a string and put its result in @s_query variable.

您正在构建一个字符串并将其结果放在@s_query变量中。

So you must convert your int variable as @PageIndex1 and @PageSize1, in this way:

因此，您必须以这种方式将int变量转换为@ PageIndex1和@ PageSize1：

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

If you want maintain the readability of your code you can define two variables:

如果要保持代码的可读性，可以定义两个变量：

DECLARE @pistr varchar(20)
DECLARE @psstr varchar(20)

SET @pistr = CONVERT(varchar(20), @PageIndex1)
SET @psstr = CONVERT(varchar(20), @PageSize1)

So your query becomes:

所以你的查询变为：

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

#2

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

#1