[原创]SQL2005分页用存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[Proc_Paging2005]
    @SqlAllFields NVARCHAR(MAX) ,--所有字段
    @SqlTablesAndWhere NVARCHAR(MAX) ,--from 之后的 order 之前的部分
    @OrderFields NVARCHAR(MAX) ,--排序用字段
    @PageSize INT ,--每页显示的数量
    @PageIndex INT OUTPUT ,--要显示的页码,设置为OUTPUT用于判断请求页码的有效性
    @RecordCount INT OUTPUT ,--总记录数
    @PageCount INT OUTPUT --总页数
AS

    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT @RecordCount = count(*) from ' + @SqlTablesAndWhere ;
    EXEC sp_executesql @sql, N'@RecordCount int output', @RecordCount OUTPUT ;

 --获得总页数
    IF @RecordCount % @PageSize = 0
        SET @PageCount = @RecordCount / @PageSize ;
    ELSE
        SET @PageCount = @RecordCount / @PageSize + 1 ;
     
    --判断当前页码
    SET @PageIndex = ISNULL(@PageIndex, 1) ;
    IF @PageIndex > @PageCount
        SET @PageIndex = @PageCount ;
    IF @PageIndex < 1
        SET @PageIndex = 1 ;

    DECLARE @start_row_num AS INT ;
    DECLARE @end_row_num AS INT ;
    SET @start_row_num = ( ( @PageIndex - 1 ) * @PageSize ) + 1 ;
    SET @end_row_num = ( @start_row_num + @PageSize ) - 1 ;

    SET @sql = 'select * from (select row_number() over (' + @OrderFields
        + ') as RowNumber,' + @SqlAllFields + ' from ' + @SqlTablesAndWhere
        + ') as temptable where RowNumber between ' + STR(@start_row_num)
        + ' and ' + STR(@end_row_num)

    EXECUTE(@sql)