分页存储过程汇总

分页存储过程  
   
  －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－  
  方法一：  
   
  CREATE   PROCEDURE   sp_page  
      @tb                   varchar(50),   --表名  
      @col                 varchar(50),   --按该列来进行分页  
      @coltype         int,                   --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型  
      @orderby         bit,                   --排序,0-顺序,1-倒序  
      @collist         varchar(800),--要查询出的字段列表,*表示全部字段  
      @pagesize       int,                   --每页记录数  
      @page               int,                   --指定页  
      @condition     varchar(800),--查询条件  
      @pages             int   OUTPUT       --总页数  
  AS  
  DECLARE   @sql   nvarchar(4000),@where1   varchar(800),@where2   varchar(800)  
  IF   @condition   is   null   or   rtrim(@condition)=''  
  BEGIN--没有查询条件  
      SET   @where1='   WHERE   '  
      SET   @where2='     '  
  END  
  ELSE  
  BEGIN--有查询条件  
      SET   @where1='   WHERE   ('+@condition+')   AND   '--本来有条件再加上此条件  
      SET   @where2='   WHERE   ('+@condition+')   '--原本没有条件而加上此条件  
  END  
  SET   @sql='SELECT   @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize   AS   varchar)+  
                    ')   FROM   '+@tb+@where2  
  EXEC   sp_executesql   @sql,N'@pages   int   OUTPUT',@pages   OUTPUT--计算总页数  
  IF   @orderby=0  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                        '   FROM   '+@tb+@where1+@col+'>(SELECT   MAX('+@col+')   '+  
                        '   FROM   (SELECT   TOP   '+CAST(@pagesize*(@page-1)   AS   varchar)+'   '+  
                        @col+'   FROM   '+@tb+@where2+'ORDER   BY   '+@col+')   t)   ORDER   BY   '+@col  
  ELSE  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                        '   FROM   '+@tb+@where1+@col+'<(SELECT   MIN('+@col+')   '+  
                        '   FROM   (SELECT   TOP   '+CAST(@pagesize*(@page-1)   AS   varchar)+'   '+  
                        @col+'   FROM   '+@tb+@where2+'ORDER   BY   '+@col+'   DESC)   t)   ORDER   BY   '+  
                        @col+'   DESC'  
  IF   @page=1--第一页  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+'   FROM   '+@tb+  
          @where2+'ORDER   BY   '+@col+CASE   @orderby   WHEN   0   THEN   ''   ELSE   '   DESC'   END  
  EXEC(@sql)  
  GO 

方法三：  
   
   
  ALTER   PROCEDURE   dbo.GetPagingRecord    
          (    
                  @tablename   varchar(100),--表名或视图表    
 @fieldlist   varchar(4000)='*',--欲选择字段列表    
                  @orderfield   varchar(100),--排序字段    
                  @keyfield   varchar(100),--主键    
                  @pageindex   int,--页号,从0开始    
                  @pagesize   int=20,--页尺寸    
                  @strwhere   varchar(4000),--条件    
                  @ordertype   bit=1--排序,1,降序,0,升序    
   
          )    
  AS    
  /**//*    
  名称:GetPagingRecord    
  作用:按任意字段进行排序分页    
  作者:菩提树(MARK   MA)    
  时间:2004-12-14    
  声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明    
  */    
          SET   NOCOUNT   ON    
   
          declare   @sqlstr   varchar(6000)    
          --处理SQL中危险字符,并且将条件处理成易嵌入的形式    
          set   @strwhere=replace(@strwhere,'''','''''')    
          set   @strwhere=replace(@strwhere,'--','')    
          set   @strwhere=replace(@strwhere,';','')    
          set   @sqlstr='declare   @CurPageNum   int;'    
          set   @sqlstr=@sqlstr+'declare   @nextpagenum   int;'    
          set   @sqlstr=@sqlstr+'set   @curpagenum='+cast(@PageIndex   as   varchar)+'*'+cast(@Pagesize   as   varchar)+';'    
          set   @sqlstr=@sqlstr+'set   @nextpagenum='+cast(@PageIndex+1   as   varchar)+'*'+cast(@Pagesize   as   varchar)+';'    
          set   @sqlstr=@sqlstr+'declare   @sqlstr   varchar(6000);'    
          if   @ordertype=1    
          begin    
          set   @sqlstr=@sqlstr+'set   @sqlstr=''select   '+@fieldlist+'   from   (   select   top   ''+cast(@nextpagenum   as   varchar)+''   *   from      
     
  '+@tablename+'     where   '+@strwhere+'   order   by   '+@orderfield+'   desc   )   as   a   where   '+@keyfield+'   not   in   (      
     
  select   top   ''+cast(@curpagenum   as   varchar)+''   '+@keyfield+'   from   '+@tablename+'   where   '+@strwhere+'      
     
  order   by   '+@orderfield+'   desc)   order   by   '+@orderfield+'   desc'';'  
          end    
          else    
          begin    
          set   @sqlstr=@sqlstr+'set   @sqlstr=''select   '+@fieldlist+'   from   (   select   top   ''+cast(@nextpagenum   as   varchar)+''   *   from      
     
  '+@tablename+'     where   '+@strwhere+'   order   by   '+@orderfield+'   asc   )   as   a   where   '+@keyfield+'   not   in   (      
     
  select   top   ''+cast(@curpagenum   as   varchar)+''   '+@keyfield+'   from   '+@tablename+'   where   '+@strwhere+'      
     
  order   by   '+@orderfield+'   asc)   order   by   '+@orderfield+'   asc'';'    
          end    
          set   @sqlstr=@sqlstr+'execute(   @sqlstr)'    
          print   @sqlstr    
          execute(@sqlstr)     

上面的两种存储过程，第一种适合排序字段是唯一性的（效率最高），第二中没有限制