SQL_SP:分頁

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