SQL简单的分页存储过程

 

1， ­

1. create procedure proc_pager  
2. (   @startIndex int,--开始记录数  
3.     @endIndex int   --结束记录数  
4. )  
5. as  
6. begin  
7. declare @indextable table(id int identity(1,1),nid int)  
8. insert into @indextable(nid) select orderid from orders order by orderid desc  
9. select *    
10. from orders o  
11. inner join @indextable i  
12. on o.orderid=i.nid  
13. where i.id between @startIndex and @endIndex  
14. order by i.id  
15. end  

2， ­

1. create procedure proc_pager1  
2. (   @pageIndex int, -- 要选择第X页的数据  
3.     @pageSize int -- 每页显示记录数  
4. )  
5. AS  
6. BEGIN  
7.     declare @sqlStr varchar(500)  
8.     set @sqlStr='select top '+convert(varchar(10),@pageSize)+  
9.     ' * from orders where orderid not in(select top '+  
10.     convert(varchar(20),(@pageIndex-1)*@pageSize)+  
11.     ' orderid from orders) order by orderid'  
12.     exec (@sqlStr)  
13. END

3，这个只适用于sql server2005  with  as 用法 ­

1. create procedure proc_pager2  
2. (   @startIndex int,--开始记录数  
3.     @endIndex int   --结束记录数  
4. )  
5. as  
6. begin  
7. WITH temptbl AS    
8. (SELECT ROW_NUMBER() OVER (ORDER BY orderid DESC) AS Row, *FROM orders)  
9. SELECT * FROM temptbl  
10. where row between @startIndex and @endIndex  
11. order by row  
12. end