SQL的魅力--SQL 2005之分页存储过程

看到很多朋友费很多劲写的分页存储过程，也出来分享一下，其实可以很简单。

 1 create procedure [dbo].[sp_GetDataList_Pager]

 2 (

 3     @pagesize int, --每页大小

 4     @pageindex int, --要提取的页码

 5     @docount bit  --是否返回记录数

 6 )

 7 /*

 8 当@docount时，返回的是记录数，当false时返回当前页下的记录集

 9 ----by JasperZhu http://www.cnblogs.com/jasperzhu 201010

 */

 as

 begin

     if(@docount=1)

     begin

         select count(*) 

         from 

         (

             select *

             from v_Article_Show

             where 1=1

         ) as myTempTable

     end

     else

     begin

         with temptbl as (

             SELECT ROW_NUMBER() OVER (ORDER BY Art_CreateTime desc)AS Row,* 

             from

             (

                 select Art_ID,Art_Name,Art_Source,Art_CreateTime

                 from v_Article_Show

                 where 1=1

             ) as myTempTable1

         )

 

         select * FROM temptbl

         where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize

     end

 end