Creating a SQL Server 2000 stored procedure to return paged resultsets.

SQL Server 2000 does not provide a way to get paged results from a stored procedure.
MySQL has a LIMIT keyword that lets you specify how many results to return and the
initial offset value - creating paged results with MySQL is simple, but unfortunately
there is no equivalent for SQL Server 2000.

SQL Server has the TOP keyword which allows you to specify how many results to
return for your query, but the inability to put a parameter in the TOP statement in
SQL Server 2000 presents a challenge when you attempt to write a stored procedure to
return a subset of the results so that you can display them in "pages" because you will have to hard code the TOP value.

By using a temp table, you can create a stored procedure that will return paged results
based on any column that you want to sort and display the data by, many of the paging
solutions that I have seen online assume that you are going to display the data sorted
by the ID column - that is a pretty bad assumption since your users want to view the
data sorted by a more meaningful column in the table.

Assume we have a table as follows:

CREATE TABLE Employee
(
EmployeeID int IDENTITY(1,1) NOT NULL,
EmployeeName nvarchar(50) NOT NULL,
EmployeeEmail nvarchar(60) NOT NULL
)

And that we want to page through the employees sorted by Email address.

Here is a sample stored proc to page your results:

CREATE PROCEDURE Employee_GetPagedData
@offset int = 0,
@numrows int = 25
AS

SELECT
IDENTITY(int, 1,1) AS RowNum,
CAST(EmployeeID AS int) AS EmployeeID,
EmployeeName,
EmployeeEmail
INTO #tempEmployee
FROM Employee WITH (NOLOCK)
ORDER BY EmployeeEmail

SELECT *
FROM #tempEmployee
WHERE RowNum > @offset
AND RowNum <= (@offset + @numrows)
ORDER BY EmployeeEmail

DROP TABLE #tempEmployee

GO