SqlServer 2000、2005分页存储过程整理第1/3页
sqlserver2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持join的，sqlserver2000的分页存储过程，也可以运行在sqlserver2005上，但是性能没有sqlserver2005的版本好。
在最后我还附带了一个二分法的分页存储过程，也很好用的说哈~~
1.SqlServer2005：
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATEproc[dbo].[up_Page2005] @TableNamevarchar(50),--表名 @Fieldsvarchar(5000)='*',--字段名(全部字段为*) @OrderFieldvarchar(5000),--排序字段(必须!支持多字段) @sqlWherevarchar(5000)=Null,--条件语句(不用加where) @pageSizeint,--每页多少条记录 @pageIndexint=1,--指定当前为第几页 @TotalPageintoutput--返回总页数 as begin BeginTran--开始事务 Declare@sqlnvarchar(4000); Declare@totalRecordint; --计算总记录数 if(@SqlWhere=''or@sqlWhere=NULL) set@sql='select@totalRecord=count(*)from'+@TableName else set@sql='select@totalRecord=count(*)from'+@TableName+'with(nolock)where'+@sqlWhere EXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数 --计算总页数 select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if(@SqlWhere=''or@sqlWhere=NULL) set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName else set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName+'with(nolock)where'+@SqlWhere --处理页数超出范围情况 if@PageIndex<=0 Set@pageIndex=1 if@pageIndex>@TotalPage Set@pageIndex=@TotalPage --处理开始点和结束点 Declare@StartRecordint Declare@EndRecordint set@StartRecord=(@pageIndex-1)*@PageSize+1 set@EndRecord=@StartRecord+@pageSize-1 --继续合成sql语句 set@Sql=@Sql+')astwhererowIdbetween'+Convert(varchar(50),@StartRecord)+'and'+Convert(varchar(50),@EndRecord) print@sql Exec(@Sql) --------------------------------------------------- If@@Error<>0 Begin RollBackTran Return-1 End Else Begin CommitTran Return@totalRecord---返回记录总数 End end
2.SqlServer2005:
/******对象:StoredProcedure[dbo].[up_Page2005V2]脚本日期:05/21/200811:27:15******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO --============================================= --Author:<Author,,Name> --Createdate:<CreateDate,,> --Description:<Description,,> --============================================= CREATEPROCEDURE[dbo].[up_Page2005V2] @TableNamevarchar(50),--表名 @Fieldsvarchar(5000)='*',--字段名(全部字段为*) @OrderFieldvarchar(5000),--排序字段(必须!支持多字段) @sqlWherevarchar(5000)=Null,--条件语句(不用加where) @pageSizeint,--每页多少条记录 @pageIndexint=1,--指定当前为第几页 @totalRecordint=0, @TotalPageintoutput--返回总页数 AS BEGIN BeginTran--开始事务 Declare@sqlnvarchar(4000); if@totalRecord<=0begin --计算总记录数 if(@SqlWhere=''or@sqlWhere=NULL) set@sql='select@totalRecord=count(*)from'+@TableName else set@sql='select@totalRecord=count(*)from'+@TableName+'with(nolock)where'+@sqlWhere EXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数 end --计算总页数 select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if(@SqlWhere=''or@sqlWhere=NULL) set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName else set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName+'with(nolock)where'+@SqlWhere --处理页数超出范围情况 if@PageIndex<=0 Set@pageIndex=1 if@pageIndex>@TotalPage Set@pageIndex=@TotalPage --处理开始点和结束点 Declare@StartRecordint Declare@EndRecordint set@StartRecord=(@pageIndex-1)*@PageSize+1 set@EndRecord=@StartRecord+@pageSize-1 --继续合成sql语句 set@Sql=@Sql+')astwhererowIdbetween'+Convert(varchar(50),@StartRecord)+'and'+Convert(varchar(50),@EndRecord) print@sql Exec(@Sql) --------------------------------------------------- If@@Error<>0 Begin RollBackTran Return-1 End Else Begin CommitTran Return@totalRecord---返回记录总数 End END GO
3.SqlServer2005:
/******对象:StoredProcedure[dbo].[up_Page2005V2_Join]脚本日期:05/21/200811:27:30******/ SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATEPROCEDURE[dbo].[up_Page2005V2_Join] @TableNamevarchar(150),--表名 @Fieldsvarchar(5000)='*',--字段名(全部字段为*) @OrderFieldvarchar(5000),--排序字段(必须!支持多字段) @sqlWherevarchar(5000)=Null,--条件语句(不用加where) @pageSizeint,--每页多少条记录 @pageIndexint=1,--指定当前为第几页 @totalRecordint=0, @TotalPageintoutput--返回总页数 AS BEGIN BeginTran--开始事务 Declare@sqlnvarchar(4000); if@totalRecord<=0begin --计算总记录数 if(@SqlWhere=''or@sqlWhere=NULL) set@sql='select@totalRecord=count(*)from'+@TableName else set@sql='select@totalRecord=count(*)from'+@TableName+'where'+@sqlWhere EXECsp_executesql@sql,N'@totalRecordintOUTPUT',@totalRecordOUTPUT--计算总记录数 end --计算总页数 select@TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if(@SqlWhere=''or@sqlWhere=NULL) set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName else set@sql='Select*FROM(selectROW_NUMBER()Over(orderby'+@OrderField+')asrowId,'+@Fields+'from'+@TableName+'where'+@SqlWhere --处理页数超出范围情况 if@PageIndex<=0 Set@pageIndex=1 if@pageIndex>@TotalPage Set@pageIndex=@TotalPage --处理开始点和结束点 Declare@StartRecordint Declare@EndRecordint set@StartRecord=(@pageIndex-1)*@PageSize+1 set@EndRecord=@StartRecord+@pageSize-1 --继续合成sql语句 set@Sql=@Sql+')astwhererowIdbetween'+Convert(varchar(50),@StartRecord)+'and'+Convert(varchar(50),@EndRecord) print@sql Exec(@Sql) --------------------------------------------------- If@@Error<>0 Begin RollBackTran Return-1 End Else Begin CommitTran Return@totalRecord---返回记录总数 End END
4.SqlServer2000: