For the page algorithm speed, the comparison of the three algorithms is as follows: ADO Recordset's Slow Slow, Nested SELECT statement paging else, the stored procedure paging is the fastest. The current network forum stored procedure version is to implement paging in the last way. Although the algorithm that has already moved the network forums can be studied, it is not beneficial to refer to the official algorithm of Microsoft MVP. The following article leads from the Microsoft TechNet Monthly Chinese Data CD Express (April 2003).
You often need to use a page form to display a result set and ensure that users can easily view each result set page, especially when you develop programs for the web site. Although you can use the ADO Recordset object to page the result set, this solution does not have scalability.
In order to solve the scalability problem, you need to include a column with a unique ID in the result set, such as a primary key in the table. The following code introduces a simple example, which uses two stored procedures to navigate between each page:
Create Procedure SpgetNextPage
@ID varchar (11) = '0',
@Rows INT = 0
AS
Set nocount on
Set rowcount @ROWS
SELECT
A.au_id,
A.au_fname '' au_lname as name
From
Authors a
WHERE
A.au_id> @ID
ORDER BY
a.au_id
SET ROWCOUNT 0
Set nocount off
Go
Create Procedure SpgetPrevpage
@ID varchar (11) = '0',
@Rows INT = 0
AS
Set nocount on
Set rowcount @ROWS
SELECT
A.au_id,
A.au_fname '' au_lname as name
INTO
#Temp
From
Authors a
WHERE
A.au_id <@ID
ORDER BY
A.au_id desc
SET ROWCOUNT 0
SELECT
*
From
#Temp
ORDER BY
AU_ID
SET ROWCOUNT 0
Set nocount off
The sample data used in this example is from the PUBS database, and you can paginate the Authors table. If you want to return the first two lines of data on the first page, you can use the SpgetNextPage stored procedure with the following parameters: exec spgetnextpage @ id = '0', @ rows = 2
spGetNextPage process will return to the first two of the authors table:
172-32-1176 Johnson White Marjorie Green
If you need to return the next two authors, you can pass the ID Id to SpgetNextPage:
EXEC SPGETNEXTPAGE @ ID = '213-46-8915', @ rows = 2
Results page shows:
238-95-7766 Cheryl Carson Michael O'Leary
If you want to move to a previous page, you can use the first line of ID call spGetprevpage:
EXEC SPGETPREVPAGE @ id = '238-95-7766', @ rows = 2 results will display the first page you see in front. One disadvantage to use this method is that columns with unique IDs determine the order of result sets. In the case of this article, the AU_ID field must be in front of the author's name field.
