Q
How can I use SQL Server to manage result set paging?

A
You often need to display a result set one page at a time while maintaining ease-of-navigation between those pages, particularly when you're working with a Web site. Although it is possible to page through result sets using the ADO Recordset object, that solution is not scalable.

To solve the problem of scalability, you need to include a unique ID column, such as a primary key, in your table. The following code shows a simple example that uses two stored procedures to navigate between pages:

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

This example, based on sample data from the pubs database, lets you page through the authors table. To return the first two rows on the first page, you use spGetNextPage with the following parameters:

EXEC spGetNextPage @id=' 0', @rows=2

The spGetNextPage procedure returns the first two authors in the table:

172-32-1176 Johnson White
Marjorie Green

To return the next two authors, pass the last row's ID to spGetNextPage:

EXEC spGetNextPage @id='213-46-8915', @rows=2

The resulting page shows:

238-95-7766 Cheryl Carson
Michael O'Leary

To move to the previous page, call spGetPrevPage with the first row's ID:

EXEC spGetPrevPage @id='238-95-7766', @rows=2

The result shows the first page, which you saw earlier. One drawback of this method is that the unique ID column dictates the order of the results. In this case, the au_id field comes before the author name.