Handy code: get pages of records from database
I finally figured out how to use a stored procedure to return only page X of a recordset. This is useful when you need to grab N records at a time (the ubiquitous "Page X of N" at the bottom of a web-based dataset).
This is coded for SQL Server 2005; the @temptable TABLE variable can easily be a temporary table; just delete it after the procedure finishes.
DECLARE @pagenumber int, @recordsperpage int DECLARE @temptable TABLE (id int) -- or use #temptable for SQL Server SET @pagenumber = 3 -- for this example, start at page 3 (check that this is 1 or greater) SET @recordsperpage = 3 -- return 3 records per page -- Get all the primary keys that we need, in descending order. Put any filtering clauses here, and sort at will INSERT INTO @temptable SELECT TOP(@pagenumber * @recordsperpage) id FROM SourceTable ORDER BY ID DESC -- Delete any records not in the requested page DELETE FROM @temptable WHERE ID IN (SELECT TOP ((@pagenumber - 1) * @recordsperpage) id FROM @temptable ORDER BY id DESC) --- this is the result set; order it the same way as the initial INSERT SELECT * FROM SourceTable WHERE id IN (SELECT id FROM @temptable) ORDER BY id DESC -- if using a temp table, DROP it here
Voila! The result set returns only the records for the requested page, in the order specified (in this example, descending order by id).
To my knowledge, this deals with the least amount of data possible. It grabs the minimum number of primary keys that encompass the page in question, deletes unnecessary keys, then the full query returns only those few records in that page.