USE [test]
GO
/****** Object: StoredProcedure [dbo].[test1] Script Date: 07/02/2013 22:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[test1]
@str VARCHAR(20) ,
@pageindex INT ,
@pagesize INT
AS
BEGIN
DECLARE @temp VARCHAR(4000)
DECLARE @startRow INT ,
@endRow INT
SET @startRow = ( @pageIndex - 1 ) * @pageSize + 1
SET @endRow = @startRow + @pageSize - 1
PRINT @startRow
PRINT @endRow
DECLARE @sql VARCHAR(2000)
SET @sql = ''
SET @sql += 'select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1'
IF @str = '1'
BEGIN
SET @sql += ' and NoSeq=5'
PRINT '@sql:' + @sql
END

SET @temp = ''
SET @temp += ' SELECT *
FROM ( ' + @sql + '
) AS T
WHERE T.RowNumber < @startRow and T.RowNumber < @endRow'
PRINT '完整:' + @temp
EXECUTE(@temp)
END

执行结果

1
10
@sql:select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1 and NoSeq=5
完整: SELECT *
FROM ( select *,ROW_NUMBER() over(order by noid ) AS RowNumber from sys_NoItems where 1=1 and NoSeq=5
) AS T
WHERE T.RowNumber < @startRow and T.RowNumber < @endRow
消息 137，级别 15，状态 2，第 4 行
必须声明标量变量 "@startRow"。

(1 行受影响)

(1 行受影响)

////////////////////////求解决方法