这个是sql2000的分页存储过程
数据表信息:Order(OrderID,CustomerID,EmployeeID,OrderDate...)这个表就是NORTHWND数据库里的Order了ALTER procedure P_GetPagedOrders2000
(@startIndex int,
@endIndex int
)
as
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select orderid from orders order by orderid desc
select O.*
from orders O
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @endIndex order by t.id
set nocount off
RETURN
declare @indextable table(id int identity(1,1),nid int)这段是新建一个名为indextable的临时表吧?
然后下面的代码,我理解得很模糊,哪位朋友解释解释,在下非常感谢
数据表信息:Order(OrderID,CustomerID,EmployeeID,OrderDate...)这个表就是NORTHWND数据库里的Order了ALTER procedure P_GetPagedOrders2000
(@startIndex int,
@endIndex int
)
as
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select orderid from orders order by orderid desc
select O.*
from orders O
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @endIndex order by t.id
set nocount off
RETURN
declare @indextable table(id int identity(1,1),nid int)这段是新建一个名为indextable的临时表吧?
然后下面的代码,我理解得很模糊,哪位朋友解释解释,在下非常感谢
set rowcount @endIndex --设置返回行数
insert into @indextable(nid) select orderid from orders order by orderid desc--将order表里的orderid插入到@indextable 表里,注意已经设置了返回的行数
select O.*
from orders O
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @endIndex order by t.id
--查询指定行数的数据
(@startIndex int,
@endIndex int
)
as
set nocount ONdeclare @indextable table(id int identity(1,1),nid int) --创建表变量:@indextable 包含有自增列idset rowcount @endIndex --设定行数上限
insert into @indextable(nid) select orderid from orders order by orderid DESC
--将order表的orderid插入至@indextable的nid中,此时生成了id的连续编号select O.*
from orders O
inner join @indextable t on O.orderid=t.nid
where t.id between @startIndex and @endIndex
order by t.id--提取id编号范围在@startIndex 及@endIndex 之间的数据
set nocount off
RETURN