Subjects for the management result set
Release Date: September 24, 2001
Q: How can I use SQL Server to manage the paging of the result set?
A: 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 = 0ASSET NOCOUNT ONSET ROWCOUNT @ rowsSELECTa.au_id, a.au_fname '' au_lname AS nameFROMauthors aWHEREa.au_id> @idORDER BYa.au_idSET ROWCOUNT 0SET NOCOUNT OFFGOCREATE PROCEDURE spGetPrevPage @ id varchar (11) = '0', @ rows int = 0ASSET NOCOUNT ONSET ROWCOUNT @ ROWSSELECTa.au_id, a.au_fname '' au_lname AS nameINTO # tempFROMauthors aWHEREa.au_id <@idORDER BYa.au_id descSET ROWCOUNT 0SELECT * From # Temporder BYAU_IDSET ROWCOUNT 0SET 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
The result will appear 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.
- SQL Server MVPS
