专用于SqlServer2005的高效分页存储过程(支持多字段任意排序,不
发布时间：2020-12-12 15:49:25 所属栏目：MsSql教程 来源：网络整理
导读：? set?ANSI_NULLS?ON set?QUOTED_IDENTIFIER?ON go --?============================================= --?Author:????????杨俊明 --?Create?date:?2006-11-05 --?Description:????高效分页存储过程，仅适用于Sql2005 --?Notes:????????排序字段强烈建议建索
|
?
set?ANSI_NULLS?ON set?QUOTED_IDENTIFIER?ON go --?============================================= --?Author:????????<杨俊明> --?Create?date:?<2006-11-05> --?Description:????<高效分页存储过程，仅适用于Sql2005> --?Notes:????????<排序字段强烈建议建索引> --?============================================= Alter?Procedure?[dbo].[up_Page2005]? ?@TableName?varchar(50),????????--表名 ?@Fields?varchar(5000)?=?'*',????--字段名(全部字段为*) ?@OrderField?varchar(5000),????????--排序字段(必须!支持多字段) ?@sqlWhere?varchar(5000)?=?Null,--条件语句(不用加where) ?@pageSize?int,????????????????????--每页多少条记录 ?@pageIndex?int?=?1?,????????????--指定当前为第几页 ?@TotalPage?int?output????????????--返回总页数? as begin ????Begin?Tran?--开始事务 ????Declare?@sql?nvarchar(4000); ????Declare?@totalRecord?int;???? ????--计算总记录数 ????????? ????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
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |