深入sql server 2005 万能分页存储过程的详解

–建立主表临时表
create table #temp
(
rownumber bigint,
orderseqno varchar(36),
goodsname varchar(50),
companyname varchar(100)
)

–建立子表临 时表
create table #detail
(
orderseqno varchar(36),
detailid uniqueidentifier,
unitprice decimal(12,2),
qty int
)

–插入主表数据到主表临时表
insert into #temp
select oo.rownumber, oo.orderseqno, oo.goodsname, oo.companyname from
(select row_number () over (order by oi.createdate desc) as rownumber,
oi.orderseqno, oi.goodsname ,ci.companyname
from orderinfo oi inner join companyinfo ci on oi.companyid=ci.companyid
where oi.createdate<getdate()
) as oo
where rownumber between 10 and 20

–定义游标
declare @temp_cursor cursor

–给游标赋值
set @temp_cursor=cursor for select #temp.orderseqno,#temp.goodsname from #temp

–定义游标循环过程中所需保存的临时数据
declare @orderseqno varchar(36),@goodsname varchar(50)

–打开游标
open @temp_cursor

fetch next from @temp_cursor into @orderseqno,@goodsname

–循环游标，查询子表数据，然后插入子表临时表
while @@fetch_status=0
begin
insert into #detail
select od.orderseqno,od.orderdetailid, od.unitprice,od.qty
from orderdetail od
where  od.orderseqno=@orderseqno

fetch next from @temp_cursor into @orderseqno,@goodsname
end

–关闭游标
close @temp_cursor
deallocate @temp_cursor

select * from #temp
select * from #detail

–删除临时表
drop table #temp
drop table #detail

上面的t-sql只在sql server 2005上调试成功。