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