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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!