几个简单的sql分页存储过程

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

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

下面这个仅适用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