实用单表千万级分页存储过程二（不敢独享，特此分享）

此存储过程适合所有排序字段，属于通用类型

本存储过程本人做了小修改

/**********************************************************************************************
********************通过指定的条件分页查询数据表【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+' <(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 @strTmp
set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
--print @strTmp

SET NOCOUNT OFF
GO