Conversion error when converting varchar value to datatype int in SQL Server 2008

Question

I do not know how to solve this error:

Conversion error while converting varchar value
'SELECT LastName, FirstName FROM ## Results WHERE ## RowNum BETWEEN (' for int data type.

Request:

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

Answer 1

You build a string and put its result in the @s_query variable.

So you have to convert the int variable as @ PageIndex1 and @ PageSize1, thus:

 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 to maintain code readability, 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 will look like this:

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

Answer 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