MSSQL2005 分页存储过程

--调用方法：exec upPageDiv kucun,'id,ItemCode,ItemName','ItemCode asc','id>0',20,1,?,?
Create Procedure [dbo].[upPageDiv]
    @TableName varchar(200),            --表名
    @Fields varchar(5000)='*',          --字段名(默认为*)
    @OrderField varchar(5000),          --排序字段(必须!支持多字段)
    @sqlWhere varchar(5000)=Null,       --条件语句(不用加where)
    @pageSize int,                      --指定每页记录条数
    @pageIndex int=1,                   --指定当前页码
    @totalPage int output,              --返回总页数
    @totalRecord int output             --返回总记录数
As
Begin
    Begin Tran  --开始事务
    Declare @sql nvarchar(4000);

    --计算总记录数
    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

    --计算总页数
    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 '+@TableName+' Where rowID Between '+Convert(varchar(50),@StartRecord)+' And '+Convert(varchar(50),@EndRecord)

    Exec(@Sql)

    If @@Error<>0
        Begin
            RollBack Tran
            Return -1
        End
    Else
        Begin
            Commit Tran
            Return @totalRecord --返回记录总数
        End
End