實用單表千萬級分頁儲存過程二（不敢獨享，特此分享）

實用單表千萬級分頁儲存過程二（不敢獨享，特此分享）

此儲存過程適合所有排序欄位，屬於通用型別

本儲存過程本人做了小修改

/**********************************************************************************************
********************通過指定的條件分頁查詢資料表【TableName or ViewName】記錄******************
**********************************************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[ThePaginationProcedureIsAllPurpose]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ThePaginationProcedureIsAllPurpose]
GO
--引數說明
-------------------------------------------------------------
/*
* @tblName   ----要顯示的表或多個表的連線
* @fldName   ----要查詢出的欄位列表,*表示全部欄位
* @pageSize   ----每頁顯示的記錄個數
* @page    ----要顯示那一頁的記錄
* @fldSort   ----排序欄位列表或條件，如：id desc (多個id desc,dt asc)
* @Sort    ----排序方法，0為升序，1為降序
*      (如果是多欄位排列Sort指代最後一個排序欄位的排列順序(最後一個排序欄位
*      不加排序標記)--程式傳參如：' SortA Asc,SortB Desc,SortC ')
* @strCondition  ----查詢條件,不需where
* @ID    ----主表的主鍵
* @Dist    ----是否新增查詢欄位的 DISTINCT 預設0不新增/1新增
* @pageCount  ----查詢結果分頁後的總頁數
* @Counts   ----查詢到的總記錄數
* @UsedTime   ----耗時測試時間差
*/
CREATE PROCEDURE [dbo].[ThePaginationProcedureIsAllPurpose]
(
 @tblName nvarchar(max),
 @fldName nvarchar(max) = '*',
 @pageSize int = 40,
 @page int = 1,
 @fldSort nvarchar(max) = null,
 @Sort bit = 1,
 @strCondition nvarchar(max) = null,
 @ID nvarchar(max),
 @Dist bit = 0,
 @pageCount int = 1 output,
 @Counts int = 1 OUTPUT,
 @UsedTime int OUTPUT
)
AS
 SET NOCOUNT ON
 Declare @sqlTmp nvarchar(max)   ----存放動態生成的SQL語句
 Declare @strTmp nvarchar(max)   ----存放取得查詢結果總數的查詢語句
 Declare @strID     nvarchar(max)  ----存放取得查詢開頭或結尾ID的查詢語句
 Declare @strSortType nvarchar(max)  ----資料排序規則A
 Declare @strFSortType nvarchar(max)  ----資料排序規則B
 Declare @SqlSelect nvarchar(max)  ----對含有DISTINCT的查詢進行SQL構造
 Declare @SqlCounts nvarchar(max)  ----對含有DISTINCT的總數查詢進行SQL構造
 Declare @timediff DATETIME    --耗時測試時間差

 select @timediff=getdate()

 if @Dist = 0
 begin
  set @SqlSelect = 'select '
  set @SqlCounts = 'Count(*)'
 end
 else
 begin
  set @SqlSelect = 'select distinct '
  set @SqlCounts = 'Count(DISTINCT '+@ID+')'
 end

 if @Sort=0
 begin
  set @strFSortType=' ASC '
  set @strSortType=' DESC '
 end
 else
 begin
  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 ' + @tblName
 end
 else
 begin
  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) ' + @strCondition
 end
 ----取得查詢結果總數量-----
 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
 declare @tmpCounts int
 if @Counts = 0
  set @tmpCounts = 1
 else
  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+' '+@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+' '+@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+' '+@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+' '+@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 @strTmp
set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗時
--print @strTmp

SET NOCOUNT OFF
GO

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-616532/，如需轉載，請註明出處，否則將追究法律責任。