Notice (8): file_put_contents(): Write of 274 bytes failed with errno=28 No space left on device [CORE/src/Log/Engine/FileLog.php, line 140]

Notice: file_put_contents() [function.file-put-contents]: Write of 1108 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 5437 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 3153 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): unserialize() [<a href='https://secure.php.net/function.unserialize'>function.unserialize</a>]: Error at offset 4079 of 4085 bytes [APP/Controller/NewsController.php, line 5571]

Notice: file_put_contents() [function.file-put-contents]: Write of 2759 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Undefined array key "nsort" [APP/Controller/NewsController.php, line 3613]

Notice: file_put_contents() [function.file-put-contents]: Write of 2075 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Trying to access array offset on value of type null [APP/Controller/NewsController.php, line 3613]

Notice: file_put_contents() [function.file-put-contents]: Write of 2099 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Undefined array key "nsort" [APP/Controller/NewsController.php, line 3613]

Notice: file_put_contents() [function.file-put-contents]: Write of 2075 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Trying to access array offset on value of type null [APP/Controller/NewsController.php, line 3613]

Notice: file_put_contents() [function.file-put-contents]: Write of 2099 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 143 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 2792 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (512): long cache was unable to write '36fc8a975a5e88edcbeca95655e180bd' to Cake\Cache\Engine\FileEngine cache [CORE/src/Cache/Cache.php, line 275]

Notice: file_put_contents() [function.file-put-contents]: Write of 2587 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Notice (8): SplFileObject::fwrite() [<a href='https://secure.php.net/splfileobject.fwrite'>splfileobject.fwrite</a>]: Write of 108 bytes failed with errno=28 No space left on device [CORE/src/Cache/Engine/FileEngine.php, line 141]

Notice: file_put_contents() [function.file-put-contents]: Write of 2792 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (512): long cache was unable to write 'd288a9bd95cb065b4ee8147b7aa3bbbc' to Cake\Cache\Engine\FileEngine cache [CORE/src/Cache/Cache.php, line 275]

Notice: file_put_contents() [function.file-put-contents]: Write of 2587 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
SqlServer2000、2005分页存储过程整理第1/3页 - 站长搜索
娱乐休闲

娱乐休闲

工商与经济

电脑与网络

公司与企业

教育与培训

文学

艺术

艺术

体育与健身

新闻与媒体

卫生与健康

科学/文化

生活与服务

绘画

绘画

求助/论坛

图书/书店

理论/评论

古董与收藏品

新闻媒体

体操

体操

科学

化工

网球

各地科技/文化

考古

首页 > 资讯列表 > 编程/数据库 >>

SqlServer2000、2005分页存储过程整理第1/3页

Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]
Notice: file_put_contents() [function.file-put-contents]: Write of 2423 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]

Notice: file_put_contents() [function.file-put-contents]: Write of 2447 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
">
Warning (2): Undefined array key "nsort" [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]

Notice: file_put_contents() [function.file-put-contents]: Write of 2423 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
Warning (2): Trying to access array offset on value of type null [ROOT/plugins/Kuhuang/templates/Websites/view.php, line 430]

Notice: file_put_contents() [function.file-put-contents]: Write of 2447 bytes failed with errno=28 No space left on device in /www/wwwroot/www.adminso.com/vendor/cakephp/cakephp/src/Log/Engine/FileLog.php on line 140
 2022-09-23 16:45:48 转载来源: 网络整理/侵权必删

sqlserver2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持join的，sqlserver2000的分页存储过程，也可以运行在sqlserver2005上，但是性能没有sqlserver2005的版本好。在最后我还附带了一个二分法的分页存储过程，也很好用的说哈~~1.SqlServer2005：SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEproc[dbo].[up_Page2005]@TableNamevarchar(50),--表名@Fieldsvarchar(5000)='*',--字段名(全部字段为*)@OrderFieldvarchar(5000),--排序字段(必须!支持多字段)@sqlWherevarchar(5000)=Null,--条件语句(不用加where)@pageSizeint,--每页多少条记录@pageIndexint=1,--指定当前为第几页@TotalPageintoutput--返回总页数asbeginBeginTran--开始事务Declare@sqlnvarch

sql server 2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持join的，sql server 2000的分页存储过程，也可以运行在sql server 2005上，但是性能没有sql server 2005的版本好。

在最后 我还附带了一个二分法的分页存储过程，也很好用的说哈~~

1.SqlServer 2005：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [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      --返回总页数asbegin  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 + ' with(nolock) 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 + ' with(nolock) 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 t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)   print @sql  Exec(@Sql)  ---------------------------------------------------  If @@Error <> 0   Begin    RollBack Tran    Return -1   End   Else   Begin    Commit Tran    Return @totalRecord ---返回记录总数   Endend

2.Sql Server 2005:

/****** 对象: StoredProcedure [dbo].[up_Page2005V2]  脚本日期: 05/21/2008 11:27:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:    <Author,,Name>-- Create date: <Create Date,,>-- Description:  <Description,,>-- =============================================CREATE PROCEDURE [dbo].[up_Page2005V2]  @TableName varchar(50),    --表名 @Fields varchar(5000) = '*',  --字段名(全部字段为*) @OrderField varchar(5000),    --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int,          --每页多少条记录 @pageIndex int = 1 ,      --指定当前为第几页 @totalRecord int = 0, @TotalPage int output      --返回总页数ASBEGIN   Begin Tran --开始事务  Declare @sql nvarchar(4000);  if @totalRecord<=0 begin    --计算总记录数    if (@SqlWhere='' or @sqlWhere=NULL)      set @sql = 'select @totalRecord = count(*) from ' + @TableName    else      set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数  end  --计算总页数  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 + ' with(nolock) 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 t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)   print @sql  Exec(@Sql)  ---------------------------------------------------  If @@Error <> 0   Begin    RollBack Tran    Return -1   End   Else   Begin    Commit Tran    Return @totalRecord ---返回记录总数   EndENDGO

3.Sql Server 2005:

/****** 对象: StoredProcedure [dbo].[up_Page2005V2_Join]  脚本日期: 05/21/2008 11:27:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[up_Page2005V2_Join]  @TableName varchar(150),    --表名 @Fields varchar(5000) = '*',  --字段名(全部字段为*) @OrderField varchar(5000),    --排序字段(必须!支持多字段) @sqlWhere varchar(5000) = Null,--条件语句(不用加where) @pageSize int,          --每页多少条记录 @pageIndex int = 1 ,      --指定当前为第几页 @totalRecord int = 0, @TotalPage int output      --返回总页数ASBEGIN   Begin Tran --开始事务  Declare @sql nvarchar(4000);  if @totalRecord<=0 begin    --计算总记录数    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--计算总记录数  end  --计算总页数  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 t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)   print @sql  Exec(@Sql)  ---------------------------------------------------  If @@Error <> 0   Begin    RollBack Tran    Return -1   End   Else   Begin    Commit Tran    Return @totalRecord ---返回记录总数   EndEND

4.Sql Server 2000:

USE [game]GO/****** 对象: StoredProcedure [dbo].                                                                                      脚本日期: 05/21/2008 11:37:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].                                                                                    @RecordCount int output,@ReturnCount bit,@QueryStr nvarchar(1000)='table1',--表名、视图名、查询语句@PageSize int=20, --每页的大小(行数)@PageCurrent int=2, --要显示的页 从0开始@FdShow nvarchar (2000)='*', --要显示的字段列表@IdentityStr nvarchar (100)='id', --主键@WhereStr nvarchar (2000)='1=1',@FdOrder nvarchar(100)='desc' --排序  只能取desc或者ascasset nocount ondeclare@sql nvarchar(2000)if @WhereStr = '' begin set @WhereStr = '1=1'endif @ReturnCount=1 begin declare @tsql nvarchar(200) set @tsql=N'select @RecordCount = count(*) from ' + @QueryStr + ' where ' + @WhereStr exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount outputendif @PageCurrent = 0 begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' ' + @FdOrderendelse begin if upper(@FdOrder) = 'DESC' begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '< ( select min(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' desc) as t) order by ' + @IdentityStr + ' desc' end else begin set @sql = 'select top ' + cast(@PageSize as nvarchar(3)) + ' ' + @FdShow + ' from ' + @QueryStr + ' where ' + @WhereStr + ' and ' + @IdentityStr + '> ( select max(' + @IdentityStr + ') from (select top ' + cast(@PageSize*@PageCurrent as nvarchar(10)) + ' ' + @IdentityStr + ' from ' + @QueryStr + ' where ' + @WhereStr + ' order by ' + @IdentityStr + ' asc) as t) order by ' + @IdentityStr + ' asc' endend--print @sqlexecute(@sql)

二分分页过程：

--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/--/*-----存储过程 分页处理 浪尘 2008-9-1修改----------*/--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/alter PROCEDURE proc_paged_2part_selectMax(@tblName   nvarchar(200),    ----要显示的表或多个表的连接@fldName   nvarchar(500) = '*',  ----要显示的字段列表@pageSize  int = 10,    ----每页显示的记录个数@page    int = 1,    ----要显示那一页的记录@fldSort  nvarchar(200) = null,  ----排序字段列表或条件@Sort    bit = 0,    ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')@strCondition  nvarchar(1000) = null,  ----查询条件,不需where@ID    nvarchar(150),    ----主表的主键@Dist         bit = 0,      ----是否添加查询字段的 DISTINCT 默认0不添加/1添加@pageCount  int = 1 output,      ----查询结果分页后的总页数@Counts  int = 1 output        ----查询到的记录数)ASSET NOCOUNT ONDeclare @sqlTmp nvarchar(1000)    ----存放动态生成的SQL语句Declare @strTmp nvarchar(1000)    ----存放取得查询结果总数的查询语句Declare @strID   nvarchar(1000)    ----存放取得查询开头或结尾ID的查询语句Declare @strSortType nvarchar(10)  ----数据排序规则ADeclare @strFSortType nvarchar(10)  ----数据排序规则BDeclare @SqlSelect nvarchar(50)     ----对含有DISTINCT的查询进行SQL构造Declare @SqlCounts nvarchar(50)     ----对含有DISTINCT的总数查询进行SQL构造declare @timediff datetime --耗时测试时间差select @timediff=getdate()if @Dist = 0begin  set @SqlSelect = 'select '  set @SqlCounts = 'Count(*)'endelsebegin  set @SqlSelect = 'select distinct '  set @SqlCounts = 'Count(DISTINCT '+@ID+')'endif @Sort=0begin  set @strFSortType=' ASC '  set @strSortType=' DESC 'endelsebegin  set @strFSortType=' DESC '  set @strSortType=' ASC 'end--------生成查询语句----------此处@strTmp为取得查询结果数量的语句if @strCondition is null or @strCondition=''   --没有设置显示条件begin  set @sqlTmp = @fldName + ' From ' + @tblName  set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName  set @strID = ' From ' + @tblNameendelsebegin  set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition  set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition  set @strID = ' From ' + @tblName + ' where (1>0) ' + @strConditionend----取得查询结果总数量-----exec sp_executesql @strTmp,N'@Counts int out ',@Counts outdeclare @tmpCounts intif @Counts = 0  set @tmpCounts = 1else  set @tmpCounts = @Counts  --取得分页总数  set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize  /**//**//**//**当前页大于总页数 取最后一页**/  if @page>@pageCount    set @page=@pageCount  --/*-----数据分页2分处理-------*/  declare @pageIndex int --总数/页大小  declare @lastcount int --总数%页大小  set @pageIndex = @tmpCounts/@pageSize  set @lastcount = @tmpCounts%@pageSize  if @lastcount > 0    set @pageIndex = @pageIndex + 1  else    set @lastcount = @pagesize  --//***显示分页  if @strCondition is null or @strCondition=''   --没有设置显示条件  begin    if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2  --前半部分数据处理      begin        if @page=1          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' order by '+ @fldSort +' '+ @strFSortType        else        begin          if @Sort=1          begin          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName            +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'            +' order by '+ @fldSort +' '+ @strFSortType          end          else          begin          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName            +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'            +' order by '+ @fldSort +' '+ @strFSortType          end        end      end    else      begin      set @page = @pageIndex-@page+1 --后半部分数据处理        if @page <= 1 --最后一页数据显示          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType        else          if @Sort=1          begin          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName            +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'            +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType          end          else          begin          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName            +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'            +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType          end      end  end  else --有查询条件  begin    if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2  --前半部分数据处理    begin        if @page=1          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where 1=1 ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType        else if(@Sort=1)        begin          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName            +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'            +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType        end        else        begin          set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName            +' where (1=1) ' + @strCondition +' order by '+ @fldSort +' '+ @strFSortType+') AS TBMinID)'            +' '+ @strCondition +' order by '+ @fldSort +' '+ @strFSortType        end    end    else    begin      set @page = @pageIndex-@page+1 --后半部分数据处理      if @page <= 1 --最后一页数据显示          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType      else if(@Sort=1)          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName            +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'            +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType      else          set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName            +' where '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName            +' where (1=1) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TBMaxID)'            +' '+ @strCondition+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType    end  end------返回查询结果-----exec sp_executesql @strTmpselect datediff(ms,@timediff,getdate()) as 耗时--print @strTmpSET NOCOUNT OFFGO


标签: SqlServer2000 2005 分页 存储 过程 整理 3页


声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！

上一篇: 使用sqlserver存储过程sp_send_dbmail发送邮件配置方法(图文)
上一篇: 必须会的SQL语句(六)数据查询

猜您喜欢

热门文章

热点

标签列表

站长搜索

http://www.adminso.com

Copyright @ 2007~2025 All Rights Reserved.

Powered By 站长搜索

会员注册

会员登陆

在线投稿

提交网站

站长资讯

网站列表

域名库

关键词库

帮助中心

免责声明

隐私保护

域名查询

广告赞助

网站地图

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持