In my new function of using SQL Server2005 to construct a paging stored procedure, I mentioned using the Row_number () function instead of top to implement the paging stored procedure.
But the time is long, and found a new problem, is the master table paging query. For example: order form and order list, request is Inquiry order, second page, 10 per page
Copy Code code as follows:
--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
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
The result is the following figure
Obviously not 10 orders, but 10 details.
Here's a little improvement on the query, using a cursor
Copy Code code as follows:
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
The results of the following figure, immediately see the effect is changed, welcome to discuss better, more accurate paging query.
The above T-SQL is only successfully debugged on SQL Server 2005.
Recommend an MS SQL Server query plan for the content, you can use it to optimize SQL, write well. References: SQL Server execution plan and SQL query optimization
