SQL Server 2000、2005分页存储过程整理第1/3页

2022-11-12 09:38:01

内容摘要

这篇文章主要为大家详细介绍了SQL Server 2000、2005分页存储过程整理第1/3页，具有一定的参考价值，可以用来参考一下。对此感兴趣的朋友，看看idc笔记做的技术笔记！ sql server

文章正文

这篇文章主要为大家详细介绍了SQL Server 2000、2005分页存储过程整理第1/3页，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

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

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

1.SqlServer 2005：

代码如下：


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE 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      --返回总页数
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 + ' 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 ---返回记录总数
   End
end

2.Sql Server 2005:

代码如下：


/****** 对象: StoredProcedure [dbo].[up_Page2005V2]  脚本日期: 05/21/2008 11:27:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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      --返回总页数
AS
BEGIN

   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 ---返回记录总数
   End
END

GO

3.Sql Server 2005:

代码如下：


/****** 对象: StoredProcedure [dbo].[up_Page2005V2_Join]  脚本日期: 05/21/2008 11:27:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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      --返回总页数
AS
BEGIN

   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 ---返回记录总数
   End
END

4.Sql Server 2000:

代码如下：


USE [game]
GO
/****** 对象: StoredProcedure [dbo].

123下一页阅读全文

对此感兴趣的朋友，看看idc笔记做的技术笔记！

脚本日期: 05/21/2008 11:37:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].

对此感兴趣的朋友，看看idc笔记做的技术笔记！

@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

上一页123阅读全文

注：关于SQL Server 2000、2005分页存储过程整理第1/3页的内容就先介绍到这里，更多相关文章的可以留意

代码注释

上一篇 SQL Server行转列之PIVOT与用法的解决办法下一篇安装sql server2008后再安装sql 2005找不到本地服务器的解决方法

作者：喵哥笔记

学的不仅是技术，更是梦想！