SQL 分页存储过程

CREATE PROCEDURE splitPage

(

@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+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

 

 

EXEC sp_ExecuteSql @Str

GO

网上的有点错误

调用测试

DECLARE

@SQL nVARCHAR(4000), --不带排序语句的SQL语句

@Page int, --页码

@RecsPerPage int, --每页容纳的记录数

@ID VARCHAR(255), --需要排序的不重复的ID号

@Sort VARCHAR(255) --排序字段及规则

SET @SQL = 'SELECT vcID,vcName,vcComment FROM sys_Role'

SET @page = 2

set @RecsPerPage = 2

set @ID = 'vcID '

SET @Sort = 'vcID ASC '

EXEC splitPage @SQL,@page,@RecsPerPage,@ID,@Sort

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+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @STR

EXEC sp_ExecuteSql @Str