SQL--分页存储过程

USE [ExamDB]
GO

/****** Object:  StoredProcedure [dbo].[SysPager]    Script Date: 07/25/2011 16:11:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--分页存储过程
CREATE PROCEDURE [dbo].[SysPager]
    @TableName VARCHAR(500) ,        --表名
    @Fields VARCHAR(5000) = '*' ,    --字段名(全部字段为*)
    @OrderField VARCHAR(5000) ,        --排序字段(必须!支持多字段)
    @sqlWhere VARCHAR(5000) = NULL ,--条件语句(不用加where)
    @pageSize INT ,                    --每页多少条记录
    @pageIndex INT = 1 ,            --指定当前为第几页
    @totalRecord INT = 0  OUTPUT ,
    @TotalPage INT OUTPUT            --返回总页数
AS 
    BEGIN
    
        BEGIN TRAN --开始事务

        DECLARE @sql NVARCHAR(4000) ; 

       SET @totalRecord =0
        IF @totalRecord <= 0 
            BEGIN
        --计算总记录数
             
                IF ( @SqlWhere = ''OR @sqlWhere = NULL) 
                    SET @sql = 'select @totalRecord = count(*) from '+ @TableName
                ELSE 
                    SET @sql = 'select @totalRecord = count(*) from ' + @TableName + '  where ' + @sqlWhere

                EXEC sp_executesql @sql, N'@totalRecord int OUTPUT', @totalRecord OUTPUT--计算总记录数 
            END

    --计算总页数
        SELECT  @TotalPage = CEILING(( @totalRecord + 0.0 ) / @PageSize)

        IF ( @SqlWhere = ''
             OR @sqlWhere = NULL
           ) 
            SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by '
                + @OrderField + ') as rowId,' + @Fields + ' from '
                + @TableName 
        ELSE 
            SET @sql = 'Select * FROM (select ROW_NUMBER() Over(order by '
                + @OrderField + ') as rowId,' + @Fields + ' from '
                + @TableName + ' where ' + @SqlWhere    
        
    
    --处理页数超出范围情况
        IF @PageIndex <= 0 
            SET @pageIndex = 1
    
        IF @pageIndex > @TotalPage 
            SET @pageIndex = @TotalPage

     --处理开始点和结束点
        DECLARE @StartRecord INT
        DECLARE @EndRecord INT
    
        SET @StartRecord = ( @pageIndex - 1 ) * @PageSize + 1
        SET @EndRecord = @StartRecord + @pageSize - 1

    --继续合成sql语句
        SET @Sql = @Sql + ') as t where rowId between '
            + CONVERT(VARCHAR(50), @StartRecord) + ' and '
            + CONVERT(VARCHAR(50), @EndRecord)
        PRINT @sql 

        EXEC(@Sql)
    ---------------------------------------------------
        IF @@Error <> 0 
            BEGIN
                ROLLBACK TRAN
                RETURN -1
            END
        ELSE 
            BEGIN
                COMMIT TRAN
                RETURN @totalRecord ---返回记录总数
                PRINT  @totalRecord
            END   
    END

GO