现在用的海量数据分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <lancanfei>
-- Create date: <2008-2-23>
-- Description: <海量数据分页存储过程>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetRecordListByPage]
 @tblName      nvarchar(200),       -- 表名
    @fldName      nvarchar(200),       -- 字段名
    @pageSize     int = 10,           -- 页尺寸
    @pageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回
    @fldSort nvarchar(100) = null,          ----排序字段列表或条件
    @Sort    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     nvarchar(1000) = '',  -- 查询条件 (注意: 不要加 where)
    @ID  nvarchar(50),  ----主表的主键
    @pageCount int = 1 output,          ----查询结果分页后的总页数
    @Counts int = 1 output           ----查询到的记录数
AS
declare @strSQL   nvarchar(4000)       -- 主语句
declare @strTmp   nvarchar(4000)      ----存放取得查询结果总数的查询语句
declare @sqlSort nvarchar(400)        -- 排序类型

 
--------生成查询语句----------此处@strTmp为取得查询结果数量的语句
if (@strWhere is null or @strWhere='') --没有设置显示条件 
   begin 
     set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName 
   end
else 
   begin 
     set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strWhere 
   end 
----取得查询结果总数量----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

if @IsReCount = 0
   begin
      ----取得分页总数----
      if @Counts <= @pageSize 
         set @pageCount = 1
      else
         begin
           set @pageCount = (@Counts / @pageSize) + 1
           if(@Counts % @pageSize=0)
              set @pageCount =@pageCount -1   
         end 
      --------生成排序方法---------
      if @Sort=0  --升序
         begin
            set @strTmp = '>(select max'  
            if not(@fldSort is null or @fldSort='')  
               set @sqlSort = ' Order by ' + @fldSort   
            else  
               set @sqlSort = ' Order by ' + @ID 
         end
      else   --降序
         begin
            set @strTmp = '<(select min' 
            if not(@fldSort is null or @fldSort='')  
               set @sqlSort = ' Order by ' + @fldSort + ' DESC'   
            else  
               set @sqlSort = ' Order by ' + @ID + ' DESC '
         end
      ----拼接查询语句----
      if @PageIndex = 1   ------如果是第一页------
         begin
            set @strTmp =''
            if @strWhere != ''
                set @strTmp = ' where ' + @strWhere
            set @strSQL = 'select top ' + str(@PageSize) + @fldName +' from ['
                + @tblName + ']' + @strTmp + ' ' + @sqlSort
         end
      else
         begin
            if @strWhere != ''
               set @strSQL = 'select top ' + str(@PageSize) + @fldName +' from ['
                   + @tblName + '] where [' + @ID + ']' + @strTmp + '(['
                   + @ID + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
                   + @ID + '] from [' + @tblName + '] where ' + @strWhere + ' '
                   + @sqlSort + ') as tblTmp) and ' + @strWhere + ' ' + @sqlSort
              
            else
               set @strSQL = 'select top ' + str(@PageSize) + @fldName + ' from ['
                   + @tblName + '] where [' + @ID + ']' + @strTmp + '(['
                   + @ID + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
                   + @ID + '] from [' + @tblName + ']' + @sqlSort + ') as tblTmp)'
                   + @sqlSort
          end
      exec (@strSQL)
   end