CREATE PROCEDURE sp_pagination(
@SQL nVARCHAR(4000), --不带排序语句的SQL语句
@Page int, --页码@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255) --排序字段及规则
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN (SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T2 ORDER BY '+@Sort+') ORDER BY '+@Sort
--PRINT @Str
EXEC sp_ExecuteSql @Str
GO