SQL SERVER 存储过程分页的3种通用方法

       以前用程序写了很多效率不错分页方法，但很麻烦也都不通用。最近抽空编写了用SQL SEVER后台存储过程实现分页通用的方法，此过程提供3种分页技术方法，可以根据具体情况采用不同的方法。

-- exec get_page 'T_Table,'*','xh',100,6,0,0,' ',' lb like ''1%'''

CREATE    PROCEDURE  get_page
@tblName     varchar ( 255 ),        --  表名
@strGetFields   varchar ( 1000 )  =   ' * ' ,   --  需要返回的列 
@fldName   varchar ( 255 ) = '' ,       --  排序的字段名
@PageSize     int   =   10 ,           --  页尺寸
@PageIndex    int   =   1 ,            --  页码
@doCount    bit   =   0 ,    --  返回记录总数, 非 0 值则返回
@OrderType   bit   =   0 ,   --  设置排序类型, 非 0 值则降序
@OtherOrder   varchar ( 200 ),   -- 其它排序字段
@strWhere    varchar ( 1500 )  =   '' ,   --  查询条件 (注意: 不要加 where)
@Type   varchar ( 1 ) = ' 1 '          -- 分页使用方法
AS
SET  NOCOUNT  ON  
declare   @strSQL     varchar ( 5000 )        --  主语句
declare   @strTmp     varchar ( 110 )         --  临时变量
declare   @strOrder   varchar ( 400 )         --  排序类型

if   @doCount   !=   0
   begin
     if   @strWhere   != ''
     set   @strSQL   =   ' select count(*) as Total from  '   +   @tblName   +   '  where  ' + @strWhere
     else
     set   @strSQL   =   ' select count(*) as Total from  '   +   @tblName   +   ''
end   
-- 以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin

if   @OrderType   !=   0
begin
     set   @strTmp   =   ' <(select min '
     if   @OtherOrder != ''  
       set   @strOrder   =   '  order by  '   +   @fldName   + '  desc, ' + @OtherOrder + '   '
     else
       set   @strOrder   =   '  order by  '   +   @fldName   + '  desc '  
     -- 如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
     set   @strTmp   =   ' >(select max '
     if   @OtherOrder != ''  
       set   @strOrder   =   '  order by  '   +   @fldName   + '  asc, ' + @OtherOrder + '   '
     else
       set   @strOrder   =   '  order by  '   +   @fldName   + '  asc '
end

if   @PageIndex   =   1
begin
     if   @strWhere   !=   ''    
     set   @strSQL   =   ' select top  '   +   str ( @PageSize )  + '   ' + @strGetFields +   '   from  '   +   @tblName   +   '  where  '   +   @strWhere   +   '   '   +  

@strOrder
      else
      set   @strSQL   =   ' select top  '   +   str ( @PageSize )  + '   ' + @strGetFields +   '   from  ' +   @tblName   +   '   ' +   @strOrder
-- 如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
-- 以下代码赋予了@strSQL以真正执行的SQL代码
     IF   @Type = ' 1 '
     BEGIN -- 方法1，采用嵌套SELECT语句与TOP方法，在数据量不大的情况效果最好。
         set   @strSQL   =   ' select top  '   +   str ( @PageSize )  + '   ' + @strGetFields +   '   from  '
             +   @tblName   +   '  where  '   +   @fldName   +   ''   +   @strTmp   +   ' ( ' +   @fldName   +   ' ) from (select top  '   +   str

(( @PageIndex - 1 ) * @PageSize )  +   '   ' +   @fldName   +   '  from  '   +   @tblName   +   @strOrder   +   ' ) as tblTmp) ' +   @strOrder
        
         if   @strWhere   !=   ''
             set   @strSQL   =   ' select top  '   +   str ( @PageSize )  + '   ' + @strGetFields +   '   from  '
                 +   @tblName   +   '  where  '   +   @fldName   +   @strTmp   +   ' ( '
                 +   @fldName   +   ' ) from (select top  '   +   str (( @PageIndex - 1 ) * @PageSize )  +   '   '
                 +   @fldName   +   '  from  '   +   @tblName   +   '  where  '   +   @strWhere   +   '   '
                 +   @strOrder   +   ' ) as tblTmp) and  '   +   @strWhere   +   '   '   +   @strOrder
         EXEC  ( @strSQL )
     END
     ELSE
     IF   @Type = ' 2 '
     BEGIN   -- 方法2，采用一个轻量级的中间表，在大数据量的情况下效果会比较明显
         CREATE   TABLE  #t(Fkey  varchar ( 1000 ))
         DECLARE   @i   int
    
         SET   @i = @PageIndex * @PageSize    
         IF   @strWhere   = ''  
             SET   @strWhere   =   ' 1=1 '
         SET   @strSQL   = ' INSERT INTO #t SELECT  ' + @fldName + '  FROM  ' + @tblName + '  WHERE  ' + @strWhere + '   ' +   @strOrder
         SET   ROWCOUNT   @i
         print   @strSQL
         EXEC ( @strSQL )
    
         SET   @i = @i - @pagesize    
         SET   ROWCOUNT   @i
         DELETE   FROM  #t
        
         -- 返回查询结果   
         SET   @strSQL   = ' SELECT  ' + @strGetFields + '  FROM  ' + @tblName + '  AS a  WHERE EXISTS(SELECT FKey FROM #t WHERE FKey=a. ' + @fldName + ' )  ' +   @strOrder
         print   @strSQL         
         EXEC ( @strSQL )
         SET   ROWCOUNT   0   
         DROP   TABLE  #t
         RETURN
     END
     ELSE
     IF   @Type = ' 3 '
     BEGIN   -- 方法3，采用取当前开始编号与结束编号进行取数据集 
         DECLARE   @TheSQL   NVARCHAR ( 4000 )
         DECLARE   @RowCount   INT
         DECLARE   @BeginID   VARCHAR ( 1000 )
         DECLARE   @EndID   VARCHAR ( 1000 )
        
         /* 开始编号 */
         SET   @RowCount   =  ( @PageIndex   - 1 ) * @PageSize   + 1
         SET   ROWCOUNT   @RowCount
         SET   @TheSQL   =   ' SELECT @BeginID =  ' + @fldName + '  FROM  ' + @tblName +   '  WHERE  ' + @strWhere + '   ' +   @strOrder
         EXEC  sp_executesql  @TheSQL ,N ' @BeginID VARCHAR(1000) output ' , @BeginID  output     
    
         /* 结束编号 */
         SET   @RowCount   =   @PageIndex   *   @PageSize
         SET   ROWCOUNT   @RowCount
         SET   @TheSQL   =   ' SELECT @EndID =  ' + @fldName + '  FROM  ' + @tblName   +   '  WHERE  ' + @strWhere + '   ' +   @strOrder
         EXEC  sp_executesql  @TheSQL ,N ' @EndID varchar(1000) output ' , @EndID  output     
         /* 返回当前页结果集 */
         SET   ROWCOUNT   @PAGESIZE  
         EXEC ( ' select  ' + @strGetFields + '  from    ' + @tblName + '  WHERE(  ' + @fldName + '  between  ''' + @EndID   + '''  and  ''' + @BeginID + ''' )  '   +   @strOrder )

         SET   ROWCOUNT   0     
         SET  NOCOUNT  OFF
         RETURN
     END

end  
end    


GO

                                                                                      作者QQ:43460043 MSN:wuchencan@hotmail.com.cn