The paging Stored Procedure of yesterday (ii) used the cursor in the paging results returned by MSSQLServer with more accuracy. Many enthusiastic Friends participated in the discussion. Thank you for your participation.
The paging stored procedure yesterday (ii) used a cursor in the paging results returned by ms SQL Server. Many enthusiastic Friends participated in the discussion. Thank you for your participation.
Someone suggested that the cursor would be poor and the row would be locked. Fortunately, I locked a temporary table instead of a data table, which does not affect the write operation of the data table.
Below is the reply from the 14th floor, which made me feel like a plug-in. So with today's simplified version, I canceled the use of the cursor. The temporary table still exists. Thank you.
In fact, you only need to divide it into two queries:
1. Use Row_Number to query the page of the primary table.
2. Row_Number: query the table by PAGE inner join. The cursor is not used.
The Code is as follows:
-- Pagination without cursors
-- First place the paging primary table in the temporary table, and then use the temporary table and sub-table joint query to obtain the sub-table information
-- Not only ensures the correctness of paging, but also includes sub-table information
Create table # order
(
Number BIGINT,
Orderseqno VARCHAR (36 ),
)
Insert into # order
SELECT * FROM (SELECT ROW_NUMBER () OVER (order by oi. CreateDate DESC) AS rownumber, oi. OrderSeqNO
FROM OrderInfo oi WHERE oi. OrderSeqNO LIKE '% 100') AS o
WHERE o. rownumber BETWEEN 10 AND 20
SELECT * FROM # order inner join OrderDetail od ON od. OrderSeqNO = # order. orderseqno
Drop table # order
The Code is as follows:
-- Select top 10 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
-- Originally we wanted 10 orders, but 10 details instead of 10 orders.
-- In fact, the subtable is paginated. The order is not the number to be displayed, and the number to be displayed is the number of details.
-- Because of the joint query results of the master table and sub-table, the relationship between the master table record and the sub-Table record is 1: N. a master table record has multiple details.
-- Create a clustered Index
-- Clustered index INDEX_OrderInfo ON OrderInfo (OrderSeqNo)
-- Display 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 left join OrderDetail od ON oi. OrderSeqNO = od. OrderSeqNO
WHERE oi. OrderSeqNO LIKE '% 100'
) AS o
WHERE rownumber BETWEEN 10 AND 20
-- Pagination without cursors
-- First place the paging primary table in the temporary table, and then use the temporary table and sub-table joint query to obtain the sub-table information
-- Not only ensures the correctness of paging, but also includes sub-table information
Create table # order
(
Number BIGINT,
Orderseqno VARCHAR (36 ),
)
Insert into # order
SELECT * FROM (SELECT ROW_NUMBER () OVER (order by oi. CreateDate DESC) AS rownumber, oi. OrderSeqNO
FROM OrderInfo oi WHERE oi. OrderSeqNO LIKE '% 100') AS o
WHERE o. rownumber BETWEEN 10 AND 20
SELECT * FROM # order inner join OrderDetail od ON od. OrderSeqNO = # order. orderseqno
Drop table # order
-- There are several ways to solve the above problems:
-- 1. First query the records of the master table based on the conditions, and then loop through the C # code. Then, query the details of each record of the master table in the database, and assign the values to the attributes.
-- 2. When using a cursor in the database storage process, you also need to first query the records of the master table, and then query the sub-table information in the process of using a cursor loop, and then in C #
-- Centralized processing
-- Obviously, the latter method reduces the database's transaction overhead and obtains the desired data at a time. I personally think it is better than the first one. You are welcome to discuss a better solution.
-- Note that ROW_NUMBER () returns a bigint type instead of an int type.
-- The following is the cursor storage process.
-- Create a temporary table for the master table
Create table # temp
(
Rownumber bigint,
Orderseqno VARCHAR (36 ),
Goodsname VARCHAR (50 ),
Companyname VARCHAR (0, 100)
)
-- Create a temporary sub-table
Create table # detail
(
Orderseqno VARCHAR (36 ),
Detailid UNIQUEIDENTIFIER,
Unitprice DECIMAL (12, 2 ),
Qty int
)
-- Insert data from the primary table to the temporary table of the primary 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 ) AS oo
WHERE rownumber BETWEEN 10 AND 20
-- Define a cursor
DECLARE @ temp_cursor CURSOR
-- Assign a value to the cursor
SET @ temp_cursor = cursor for select # temp. orderseqno, # temp. goodsname FROM # temp
-- Defines the temporary data to be saved during the cursor Loop
DECLARE @ orderseqno VARCHAR (36), @ goodsname varchar (50)
-- Open the cursor
OPEN @ temp_cursor
Fetch next from @ temp_cursor INTO @ orderseqno, @ goodsname
-- Cyclically cursors: queries data in the sub-table and inserts a temporary table into the sub-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 the cursor
CLOSE @ temp_cursor
DEALLOCATE @ temp_cursor
SELECT * FROM # temp
SELECT * FROM # detail
-- Delete a temporary table
Drop table # temp
Drop table # detail
