欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SqlServer 2000、2005分頁存儲過程整理第3/3頁

 更新時(shí)間：2015年02月15日 10:20:04   投稿：junjie  
這篇文章主要介紹了SqlServer 2000、2005分頁存儲過程整理,本文給出了4個實(shí)現(xiàn)版本,還給出二分分頁過程,需要的朋友可以參考下
@RecordCount int output,@ReturnCount bit,@QueryStr nvarchar(1000)='table1',--表名、視圖名、查詢語句@PageSize int=20, --每頁的大小(行數(shù))@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創(chuàng)建 -------*/--/*-----存儲過程 分頁處理 浪塵 2008-9-1修改----------*/--/*----- 對數(shù)據(jù)進(jìn)行了2分處理使查詢前半部分?jǐn)?shù)據(jù)與查詢后半部分?jǐn)?shù)據(jù)性能相同 -------*/alter PROCEDURE proc_paged_2part_selectMax(@tblName   nvarchar(200),    ----要顯示的表或多個表的連接@fldName   nvarchar(500) = '*',  ----要顯示的字段列表@pageSize  int = 10,    ----每頁顯示的記錄個數(shù)@page    int = 1,    ----要顯示那一頁的記錄@fldSort  nvarchar(200) = null,  ----排序字段列表或條件@Sort    bit = 0,    ----排序方法，0為升序，1為降序(如果是多字段排列Sort指代最后一個排序字段的排列順序(最后一個排序字段不加排序標(biāo)記)--程序傳參如：' SortA Asc,SortB Desc,SortC ')@strCondition  nvarchar(1000) = null,  ----查詢條件,不需where@ID    nvarchar(150),    ----主表的主鍵@Dist         bit = 0,      ----是否添加查詢字段的 DISTINCT 默認(rèn)0不添加/1添加@pageCount  int = 1 output,      ----查詢結(jié)果分頁后的總頁數(shù)@Counts  int = 1 output        ----查詢到的記錄數(shù))ASSET NOCOUNT ONDeclare @sqlTmp nvarchar(1000)    ----存放動態(tài)生成的SQL語句Declare @strTmp nvarchar(1000)    ----存放取得查詢結(jié)果總數(shù)的查詢語句Declare @strID   nvarchar(1000)    ----存放取得查詢開頭或結(jié)尾ID的查詢語句Declare @strSortType nvarchar(10)  ----數(shù)據(jù)排序規(guī)則ADeclare @strFSortType nvarchar(10)  ----數(shù)據(jù)排序規(guī)則BDeclare @SqlSelect nvarchar(50)     ----對含有DISTINCT的查詢進(jìn)行SQL構(gòu)造Declare @SqlCounts nvarchar(50)     ----對含有DISTINCT的總數(shù)查詢進(jìn)行SQL構(gòu)造declare @timediff datetime --耗時(shí)測試時(shí)間差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為取得查詢結(jié)果數(shù)量的語句if @strCondition is null or @strCondition=''   --沒有設(shè)置顯示條件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----取得查詢結(jié)果總數(shù)量-----exec sp_executesql @strTmp,N'@Counts int out ',@Counts outdeclare @tmpCounts intif @Counts = 0  set @tmpCounts = 1else  set @tmpCounts = @Counts  --取得分頁總數(shù)  set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize  /**//**//**//**當(dāng)前頁大于總頁數(shù) 取最后一頁**/  if @page>@pageCount    set @page=@pageCount  --/*-----數(shù)據(jù)分頁2分處理-------*/  declare @pageIndex int --總數(shù)/頁大小  declare @lastcount int --總數(shù)%頁大小  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=''   --沒有設(shè)置顯示條件  begin    if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2  --前半部分?jǐn)?shù)據(jù)處理      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 --后半部分?jǐn)?shù)據(jù)處理        if @page <= 1 --最后一頁數(shù)據(jù)顯示          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  --前半部分?jǐn)?shù)據(jù)處理    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 --后半部分?jǐn)?shù)據(jù)處理      if @page <= 1 --最后一頁數(shù)據(jù)顯示          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------返回查詢結(jié)果-----exec sp_executesql @strTmpselect datediff(ms,@timediff,getdate()) as 耗時(shí)--print @strTmpSET NOCOUNT OFFGO


相關(guān)文章

  • SQLite3數(shù)據(jù)庫的介紹和使用教程(面向業(yè)務(wù)編程-數(shù)據(jù)庫)

    SQLite3數(shù)據(jù)庫的介紹和使用教程(面向業(yè)務(wù)編程-數(shù)據(jù)庫)

    這篇文章主要介紹了SQLite3數(shù)據(jù)庫的介紹和使用（面向業(yè)務(wù)編程-數(shù)據(jù)庫）,本文從SQLite3的庫的獲取、工程管理、SQL語句介紹、C語言編程四個角度闡述了SQLite3數(shù)據(jù)庫的實(shí)際應(yīng)用，需要的朋友可以參考下
    2023-05-05
  • SQL Server刪除表及刪除表中數(shù)據(jù)的方法

    SQL Server刪除表及刪除表中數(shù)據(jù)的方法

    本文介紹SQL Server中如何刪除表，如何刪除表中的數(shù)據(jù)。在刪除表數(shù)據(jù)時(shí)有delete和truncate兩種方法，delete和truncate有什么區(qū)別呢
    2016-04-04
  • SQLServer 數(shù)據(jù)庫的數(shù)據(jù)匯總完全解析(WITH ROLLUP)

    SQLServer 數(shù)據(jù)庫的數(shù)據(jù)匯總完全解析(WITH ROLLUP)

    乍一看，好像很容易，用group by好像能實(shí)現(xiàn)？但仔細(xì)研究下去，你又會覺得group by也是無能為力，總欠缺點(diǎn)什么，無從下手。那么，到底該如何做呢？別急，SQL Server早就幫我們做好了，下面，跟我來。
    2010-09-09
  • oracle忘記sys/system/scott用戶密碼的解決方法

    oracle忘記sys/system/scott用戶密碼的解決方法

    一、忘記除SYS、SYSTEM用戶之外的用戶的登錄密碼。 二、忘記SYS用戶，或者是SYSTEM用戶的密碼。 三、如果SYS,SYSTEM用戶的密碼都忘記或是丟失。 解決方法
    2013-03-03
  • ASP.NET和MSSQL高性能分頁實(shí)例代碼

    ASP.NET和MSSQL高性能分頁實(shí)例代碼

    這篇文章主要介紹了ASP.NET和MSSQL高性能分頁實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下
    2016-01-01
  • SQL寫法--行行比較

    SQL寫法--行行比較

    行行比較是 SQL 規(guī)范，關(guān)系型數(shù)據(jù)庫都應(yīng)該支持這種寫法，本文通過代碼以及與其他寫法進(jìn)行比較來具體講解這一規(guī)范，大家跟隨小編一起來學(xué)習(xí)吧
    2021-08-08
  • SQL的常用數(shù)據(jù)類型列表詳解

    SQL的常用數(shù)據(jù)類型列表詳解

    本篇文章通過列表方式給大家詳細(xì)介紹了SQL的常用數(shù)據(jù)類型，如果你正巧學(xué)習(xí)到這些知識點(diǎn)，參考下吧。
    2018-02-02
  • 詳解將DataGrip連接到MS SQL Server的方法

    詳解將DataGrip連接到MS SQL Server的方法

    這篇文章主要介紹了詳解將DataGrip連接到MS SQL Server的方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-11-11
  • Sql奇技淫巧之ROWNUM偽列的使用

    Sql奇技淫巧之ROWNUM偽列的使用

    本文主要介紹了Sql奇技淫巧之ROWNUM偽列，ROWNUM是一個偽列，它是根據(jù)每次查詢的結(jié)果動態(tài)生成的一列遞增編號，下面就一起來介紹一下如何使用把
    2023-08-08
  • T-SQL查詢?yōu)楹紊饔肐N和NOT?IN詳解

    T-SQL查詢?yōu)楹紊饔肐N和NOT?IN詳解

    IN和NOT?IN是比較常用的關(guān)鍵字,為什么要盡量避免呢?這篇文章主要給大家介紹了關(guān)于T-SQL查詢?yōu)楹紊饔?IN和NOT?IN的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2022-02-02

最新評論