支持排序和不排序的海量数据分页存储过程

CREATE PROCEDURE [dbo].[Proc_Pagination]
 @keyName   varchar(255),
 @tblName   varchar(255),
 @strGetFields varchar(1000) = '*',
 @PageSize   int = 10,
 @PageIndex  int = 1,
 @PageCount  int = 1 output,
 @DataCount  decimal = 1 output,
 @SortName varchar(255)='',
 @SortType varchar(255) = 'Asc',
 @SearchCondition  varchar(1500) = ''
AS
 declare @strSQL   Nvarchar(4000)
 declare @strTmp   varchar(110)
 declare @strOrder varchar(400)
 declare @seaOrder varchar(400)
 begin  
 if @SearchCondition !=''  
  set @strSQL = 'select @DataCount=COUNT(*) from [' + @tblName + '] where '+@SearchCondition
  else
   set @strSQL = 'select @DataCount=COUNT(*) from [' + @tblName + ']'
   EXEC SP_EXECUTESQL @strSQL,N'@DataCount int out ',@DataCount out

  set @PageCount=(@DataCount+@PageSize-1)/@PageSize
 
 if @PageIndex>@PageCount
  SET @PageIndex=@PageCount
end
if @SortName!=''
  begin
   if @SortType = 'Desc'
    begin
     set @strOrder = ' order by [' + @SortName +'] Desc'
     if @PageIndex*2<@PageCount
      set @seaOrder = ' order by [' + @SortName +'] Desc'
     else
      set @seaOrder = ' order by [' + @SortName +'] Asc'
    end
   else
    begin
     set @strOrder = ' order by [' + @SortName +'] Asc'
     if @PageIndex*2<@PageCount
      set @seaOrder = ' order by [' + @SortName +'] Asc'
     else
      set @seaOrder = ' order by [' + @SortName +'] Desc'
    end
    if @PageIndex = 1
    begin
    if @SearchCondition != ''
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where ' + @SearchCondition + ' ' + @strOrder
    else
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName + '] '+ @strOrder
    end
   else
    begin
    if @PageIndex*2<@PageCount
     if @SearchCondition = ''
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where [' + @keyName + '] not in(select ['+ @keyName + '] from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + ']' + @seaOrder + ') as tblTmp)'+ @strOrder
      else
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] not in(select [' + @keyName + '] from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ' + @seaOrder + ') as tblTmp) and ' + @SearchCondition + ' ' + @strOrder
    else
     if @SearchCondition = ''
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where [' + @keyName + '] in(select ['+ @keyName + '] from (select top ' + str(@DataCount-(@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + ']' + @seaOrder + ') as tblTmp)'+ @strOrder
      else
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] in(select [' + @keyName + '] from (select top ' + str(@DataCount-(@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ' + @seaOrder + ') as tblTmp) and ' + @SearchCondition + ' ' + @strOrder
    end
   end
else
  begin
   if @PageIndex = 1
    begin
    if @SearchCondition != ''
       set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName + '] where ' + @SearchCondition
    else
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName + ']'
    end
   else
    begin
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['
      + @tblName + '] where [' + @keyName + '] >(select max(['+ @keyName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @keyName + '] from [' + @tblName + '] ORDER BY ' + @keyName + ') as tblTmp)'
     if @SearchCondition != ''
      set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @keyName + '] >(select max([' + @keyName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @keyName + '] from [' + @tblName + '] where ' + @SearchCondition + ' ORDER BY ' + @keyName + ') as tblTmp) and ' + @SearchCondition
    end
   end
EXEC SP_EXECUTESQL @strSQL