Pragmatic Prose

A great paging mechanism for SQL Server and ASP.NET

One of the best (not to mention easiest) paging methods I've seen.  In fact it requires no temp tables or table variables. 

Paging is something that is so common yet seems to hard to get right and keep performant that it pays to have a nice easy template to start from.  I've posted this mainly for my own benefit so I have a permanant bookmark to this code.

The example uses a stored procedure, but its important to understand that a stored procedure is not required to make this work.

Here's the code:

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]

(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

    -- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO