Paging & sorting with an existing stored procedure

Question

I have a legacy stored procedure that returns a large resultset. The problem is that the resultset is too large to get all at once and then do paging and sorting on the web side.

Without changing the stored procedure is it possible to wrap it in some other sql code to get only certain results back in a fast manner.

Something like:

EXEC the_sp LIMIT 50 ORDER BY A

Answer 1

Without altering the stored procedure code your options are pretty limited.

You could do

CREATE TABLE #results(
    [rownum] [int] identity(1,1) NOT NULL,
    ...Other Columns matching stored procedure result format
) 

insert into #results 
EXECUTE dbo.the_sp

SELECT * FROM #results 
WHERE rownum BETWEEN 50 AND 100

Obviously it will still end up doing the same amount of work in the stored proc itself (and add some overhead for the temp table step) but it will reduce the amount of results going back to the client.

To mitigate this, and dependant on the stored procedure logic, you might be able to use

SET ROWCOUNT 100
EXECUTE dbo.the_sp

To stop it returning unneeded results belonging to pages after the one being displayed

But as this will apply to every statement in the stored proc (not just the final one returning results to the client) this may well not be suitable.

Answer 2

Please see: SQL Server 2005 Paging – The Holy Grail (requires free registration) (and applies to SQL Server 2005 onwards)