Paging stored Procedure (iii) creating more accurate paging results in SQL Server

Some people put forward the cursor is not good, will lock the row, luckily I locked is the temporary table, is not the data table, does not affect the data table's write operation.

Here is the 14 floor of the reply, let me enlightened, so with today's improved version, the use of a cursor cancellation, temporary table or exist, thank you.

In fact, you only have to split into two times query can:
1, or use Row_number to check the main Table page paging
2, Row_number Check the Main Table page inner join schedule. The cursor is not used.

Copy CodeThe code is as follows:
--No paging with cursors
--Place the primary table of the paging in a temporary table, and then use a union query for temporary and child tables to get the child table information
--it guarantees the correctness of pagination and also includes child table information.
CREATE TABLE #order
(
Number BIGINT,
Orderseqno VARCHAR (36),
)
INSERT INTO #order
SELECT * FROM (select Row_number (). CreateDate DESC) as Rownumber,oi. Orderseqno
From OrderInfo Oi WHERE oi. Orderseqno like '%2% ') as O
WHERE O.rownumber BETWEEN 20

SELECT * from #order INNER JOIN orderdetail od on od. orderseqno= #order. orderseqno

DROP TABLE #order


Copy CodeThe code is as follows:
--select top Oi. Orderseqno, Oi. Goodsname, ci.companyname,od.*
--from OrderInfo oi INNER JOIN companyinfo ci on Oi.companyid=ci.companyid
--left JOIN orderdetail od on Oi. Orderseqno=od. Orderseqno


--use Row_unmber () to implement paging
The result we wanted was 10 orders, but not 10 orders, but 10 details.
--in fact, the child table for the paging, the order is not to display the number of the number is the number of details
-Just because of the results of the primary and child table union queries, the primary and child table records are 1:n relationships, and a master table record has multiple details

--Establish a clustered index
--CLUSTERED INDEX index_orderinfo on OrderInfo (orderseqno)
--Show query execution plan
--set STATISTICS IO on

SELECT * FROM
(SELECT row_number () over (order by Oi.createdate DESC) as Rownumber,oi.orderseqno, OD. Orderdetailid
From OrderInfo Oi the left JOIN orderdetail od on Oi. Orderseqno=od. Orderseqno
WHERE Oi. Orderseqno like '%2% '
) as O
WHERE RowNumber BETWEEN 20

--No paging with cursors
--Place the primary table of the paging in a temporary table, and then use a union query for temporary and child tables to get the child table information
--it guarantees the correctness of pagination and also includes child table information.
CREATE TABLE #order
(
Number BIGINT,
Orderseqno VARCHAR (36),
)
INSERT INTO #order
SELECT * FROM (select Row_number (). CreateDate DESC) as Rownumber,oi. Orderseqno
From OrderInfo Oi WHERE oi. Orderseqno like '%2% ') as O
WHERE O.rownumber BETWEEN 20

SELECT * from #order INNER JOIN orderdetail od on od. orderseqno= #order. orderseqno

DROP TABLE #order
To solve the above problems, there are the following methods
--1, first query the main table records based on the criteria, and then loop through the C # code, again to the database to query the details of each master table record, and then assign the value to the property
--2, the use of cursors in the stored procedure of the database, is also the first query of the master table records, and then use the cursor loop in the process of querying the child table information, and then in C #
--Centralized processing
Obviously, the latter reduces the transaction cost of the database, one time to obtain the desired data, personally think it is better than the first, welcome everyone to discuss a better way

Note that the type returned by Row_number () is bigint, not int
--The following is the cursor's stored procedure


--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 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

--Defining cursors
DECLARE @temp_cursor Cursor
--Assign a value to a cursor
SET @temp_cursor =cursor for SELECT #temp. Orderseqno, #temp. Goodsname from #temp

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

--Open cursor
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 cursor
Close @temp_cursor
Deallocate @temp_cursor

SELECT * from #temp
SELECT * from #detail
--Delete temporary tables
DROP TABLE #temp
DROP TABLE #detail