简单的 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