Drill-down to SQL Server 20.05 million paging stored procedures _mssql

– Create a primary table temp table
CREATE TABLE #temp
(
RowNumber bigint,
Orderseqno VARCHAR (36),
Goodsname VARCHAR (50),
CompanyName VARCHAR (100)
)

– Create a child table temporary table
CREATE TABLE #detail
(
Orderseqno VARCHAR (36),
Detailid uniqueidentifier,
UnitPrice DECIMAL (12,2),
Qty int
)

– Insert primary table data into the primary table temp table
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 20

– Define Cursors
DECLARE @temp_cursor Cursor

– Assign a value to a cursor
SET @temp_cursor =cursor for SELECT #temp. Orderseqno, #temp. Goodsname from #temp

– Define the temporary data that is required to be saved during a cursor loop
DECLARE @orderseqno VARCHAR, @goodsname VARCHAR (50)

– Open Cursors
OPEN @temp_cursor

FETCH NEXT from @temp_cursor into @orderseqno, @goodsname

– Loop cursor, query child table data, and insert child table temporary table
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 Cursors
Close @temp_cursor
Deallocate @temp_cursor

SELECT * from #temp
SELECT * from #detail

– Delete temporary tables
DROP TABLE #temp
DROP TABLE #detail

The above T-SQL is only successfully debugged on SQL Server 2005.