简单的 sql分页存储过程

【1】

create procedure proc_pager1  

(   @pageIndex int, -- 要选择第X页的数据 

    @pageSize int -- 每页显示记录数 

)  

AS 

BEGIN 

   declare @sqlStr varchar(500)  

   set @sqlStr='select top '+convert(varchar(10),@pageSize)+  

    ' * from orders where orderid not in(select top '+  

    convert(varchar(20),(@pageIndex-1)*@pageSize)+  

    ' orderid from orders) order by orderid' 

    exec (@sqlStr)  

END 

【2】

 create procedure proc_pager  

(   @startIndex int,--开始记录数 

    @endIndex int   --结束记录数 

)  

as 

begin 

declare @indextable table(id int identity(1,1),nid int)  

insert into @indextable(nid) select orderid from orders order by orderid desc 

select *   

from orders o  

inner join @indextable i  

on o.orderid=i.nid  

where i.id between @startIndex and @endIndex  

order by i.id  

end 

 

【3】适用于sql2005 

create procedure proc_pager2  

(   @startIndex int,--开始记录数 

    @endIndex int   --结束记录数 

)  

as 

begin 

WITH temptbl AS   

(SELECT ROW_NUMBER() OVER (ORDER BY orderid DESC) AS Row, *FROM orders)  

SELECT * FROM temptbl  

where row between @startIndex and @endIndex  

order by row  

end