Making Computers Behave

Sadly, SQL Server 2000 has no built in way to page the results of a query. That is, there is no simple way to ask for rows 101 to 125 for a SELECT statement. There are many methods out there for writing a stored procedure that will return specified rows for a specific SELECT statement. I didn't want to bother with writing a stored procedure for each different SELECT statement that I have to page. Thus, I wanted a fully dynamic stored procedure that would handle this. I envisioned a stored procedure that I could pass a SELECT statement and the first and last rows desired and it would return only those rows.

After much trial and error, I came up with the following stored procedure. It brings together many different ideas that I read on other websites, but sadly I have since lost the references.

CREATE PROCEDURE spReturnPage
  (
    @SelectStatement NVARCHAR(1000), --The select statment for the desired query
                                     --MUST contain the phrase SELECT TOP 100 PERCENT
    @FirstRow INT, --The first row to fetch
    @LastRow INT --The last row to fetch
  )
AS
BEGIN
  SET NOCOUNT ON --Speed up by not having it count the number of rows returned

  SET ROWCOUNT @LastRow --Set the maximum number of rows returned to be the last one desired

  DECLARE @SQL NVARCHAR(1200) --Create a variable to store the SQL statment that does the paging

  --Create a temporary table called #TempTable which consists of the query passed in
  --plus an additional column containing the row number
  SET @SQL = 'SELECT IDENTITY(INT,1,1) AS RowNumber, DummyTable.* INTO #TempTable FROM (' + @SelectStatement + ') AS DummyTable; '
  --Select everything from the temporary table, which is returned to the user
  --This must be done as part of the dynamic sql because of the scope of #TempTable
  SET @SQL = @SQL + 'SELECT * FROM #TempTable WHERE RowNumber >= ' + CONVERT(NVARCHAR(10), @FirstRow)

  --Run the SQL statement using sp_executesql so there is some hope of optimizing
  EXEC sp_executesql @SQL
END

Basically, this procedure takes the first LastRow records of the SELECT statement and adds a column, which is the record/row number. Then it selects only those records with the RowNumber at least FirstRow.

Note that the SELECT statement that is passed to the procedure must begin with SELECT TOP 100 PERCENT and must not end with a ;. SQL Server will complain if there is a subquery like this that uses ORDER BY without the TOP 100 PERCENT.

This stored procedure can then be easily linked to a DataGrid in ASP.NET using the CustomPaging features.

While this stored procedure works, there are two important notes:

1. This procedure is not guaranteed to work. It is assumed that the SELECT statement provided contains an ORDER BY clause, which will order the results of the subquery in the stored procedure. There is no guarantee, however, that SQL Server will not reorder the results in any way it pleases to "optimize" the overall query. I have used this procedure in a number of places and have never seen it order any differently than intended, but there is still that chance.

2. If you are using this procedure in any natural application, it is likely that this is being called several times with the same SELECT statement, but different FirstRow and LastRow values. Stored procedures and views are created often because they run faster than regular SELECT statements. This is because SQL Server can optimize them and pre-compile them as well as keep usage statistics to tune this optimization over time. There is no such advantage with this procedure because the SELECT statement is not actually part of the procedure, but rather created on the fly. This is a trade-off for getting such a generic (dynamic) procedure.