通用分页存储过程 采用ROW_NUMBER(),支持2005及以后的版本

--通用分页存储过程 采用ROW_NUMBER(),支持2005及以后的版本
CREATE PROCEDURE PR_PAGE
    @TableName varchar( 5000), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
    @Fields varchar( 5000), --表中的字段，可以使用*代替
    @OrderField varchar( 5000), --要排序的字段
    @SqlWhere varchar( 5000), --WHERE子句
    @PageSize  int, --分页的大小
    @PageIndex  int, --要显示的页的索引
    @TotalPage  int  --页的总数
    --@TotalPage  int output --页的总数
 AS
 BEGIN
     --BEGIN TRAN
     
     DECLARE @sql NVARCHAR( 4000);
     DECLARE @totalRecord INT ; --记录总数
     
     IF(@SqlWhere IS NULL or @SqlWhere =  '')
        SET @SqlWhere= ''
     ELSE
        SET @SqlWhere= '  WHERE  '+@SqlWhere
    
     --利用WHERE子句进行过滤
     SET @sql =  ' SELECT @totalRecord = COUNT(1) FROM  ' + @TableName + @SqlWhere
  
     --执行sql语句得到记录总数
     EXEC sp_executesql @sql,N ' @totalRecord int OUTPUT ',@totalRecord OUTPUT
 
     SELECT @TotalPage=CEILING((@totalRecord+ 0.0)/@PageSize)
 
     --根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
     SET @sql =  ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY  ' + @OrderField +  ' ) AS rowId, ' + @Fields +  '  FROM  ' + @TableName + @SqlWhere

     --确保当前页的索引在合理的范围之内
     IF @PageIndex<= 0
        Set @PageIndex =  1
     IF @PageIndex>@TotalPage
        SET @PageIndex = @TotalPage
 
     --得到当前页在整个结果集中准确的ROW_NUMBER值
     DECLARE @StartRecord INT
     DECLARE @EndRecord INT
     SET @StartRecord = (@PageIndex- 1)*@PageSize +  1
     SET @EndRecord = @StartRecord + @PageSize -  1
 
     --输出当前页中的数据
     SET @Sql = @Sql +  ' ) AS t ' +  '  WHERE rowId BETWEEN  ' + Convert(varchar( 50),@StartRecord) +  '  AND  ' +   Convert(varchar( 50),@EndRecord)
     EXEC(@Sql)
     SELECT @totalRecord
     --IF @@Error <>  0
     --   BEGIN
     --       ROLLBACK TRAN  
     --       RETURN - 1 
     --   END 
     --ELSE 
     --   BEGIN 
     --       Commit TRAN 
     --       RETURN @totalRecord
     --   END
 END
 
EXEC PR_PAGE  ' Student inner join Sex on Student.sexid=sex.sexid ', ' ID,Code,Name,Age,Birthday ', ' ID desc ', ' STUDENT.SEXID=0 ', 10, 10000, 0
 
--DECLARE @TotalPage INT
--EXEC PR_PAGE  ' Student inner join Sex on Student.sexid=sex.sexid ', ' ID,Code,Name,Age,Birthday ', ' ID desc ', ' STUDENT.SEXID=0 ', 10, 10000,@TotalPage OUTPUT
--SELECT @TotalPage

转载于:https://www.cnblogs.com/puke/archive/2012/09/04/2670180.html