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
[color=blue]下面这个仅适用sql2005[/color]
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