What is a good way to paginate out of SQL 2000 using Start and Length parameters?

I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting options available.

The first result out of google seems like a good method but I think the example is wrong in that the 2nd sort needs to be reversed and the case when the start is less than the pagelength breaks down. The 2nd example on that page also seems like a good method but the SP is taking a pageNumber rather than the start record. And the whole temp table thing seems like it would be a performance drain.

I am making progress going down this path but it seems slow and confusing and I am having to do quite a bit of REPLACE methods on the Sort order to get it to come out right.

Are there any other easier techniques I am missing?

From stackoverflow

• No, I'm afraid not - SQL Server 2000 doesn't have any of the 2005 niceties like Common Table Expression (CTE) and such..... the method described in the Google link seems to be one way to go.

  Marc

• Also take a look here http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

  scroll down to Stored Procedure Methods

  Jeff Martin : is there one in particular you are looking at? some of these seem Access specific. I see that there are some timings and maybe the temp table isn't a bad way to go. Not sure if it makes the SP any more readable tho.

• Depending on your application architecture (and your amount of data, it's structure, DB server load etc.) you could use the DB access layer for paging.

  For example, with ADO you can define a page size on the record set (DataSet in ADO.NET) object and do the paging on the client. Classic ADO even lets you use a server side cursor, though I don't know if that scales well (I think this was removed altogether in ADO.NET).

  MSDN documentation: Paging Through a Query Result (ADO.NET)

• After playing with this for a while there seems to be only one way of really doing this (using Start and Length parameters) and that's with the temp table.

  My final solution was to not use the @start parameter and instead use a @page parameter and then use the

      SET @sql = @sql + N'
  
      SELECT * FROM
      (
      SELECT TOP ' + Cast( @length as varchar) + N' * FROM 
              (
              SELECT TOP ' + Cast( @page*@length as varchar) + N'
                   field1,
                   field2 
                   From Table1
                   order by field1 ASC
              )  as Result   
              Order by Field1 DESC
       )  as Result
       Order by Field 1 ASC'
  

  The original query was much more complex than what is shown here and the order by was ordered on at least 3 fields and determined by a long CASE clause, requiring me to use a series of REPLACE functions to get the fields in the right order.

• We've been using variations on this query for a number of years. This example gives items 50,000 to 50,300.

  select top 300 
    Items.*
  from Items
  where 
    Items.CustomerId = 1234 AND
    Items.Active = 1 AND
    Items.Id not in
    ( 
      select top 50000 Items.Id
      from Items
      where 
        Items.CustomerId = 1234 AND
        Items.Active = 1
      order by Items.id
    )
  order by Items.Id
  

• There are two SQL Server 2000 compliant answers in this StackOverflow question - skip the accepted one, which is 2005-only: