SQL Server pagination of a result set

I have a very meaty stored procedure in a SQL Server 2000 DB which returns a single resultset. I don't want to (not allowed to) touch the original SP but would like add pagination to the returned records.

Is it possible to wrap this SP with another that开发者_如何学JAVA takes the returned resultset and only gives me rows X to Y ?

create procedure ProcWrap
as

declare @T table (ID int, Name nvarchar(50))

insert into @T
exec ProcToWrap

select *
from @T
where ID < 10

Edit 1 Don't have SQL Server 2000 to test on and I don't remember if table variables where available then. Here is a procedure using a temp table instead. Added a RowNum identity column that you can use for pagination.

create procedure ProcWrap2
as

create table #T (RowNum int identity, ID int, Name nvarchar(50))

insert into #T
exec ProcToWrap

select *
from #T
where RowNum between 10 and 19

drop table #T

Edit 2 Output from ProcToWrap in this case is columns ID and Name. RowNum is generated automatically.

Get the results from the SP and put them in a temporary table, then you can select X results from that table.

As others have said you will have to put the results of the procedure in a temp table then select the rows you want from that.

To get a set of rows from your results you need to use the ROW_NUMER() function:

SELECT
   ROW_NUMBER() OVER (ORDER BY ID) AS row_number, *
FROM
   Your_Temp_Table
WHERE row_number BETWEEN 11 AND 20 -- For the second page of results with 10 per page.

EDIT: Just realised you are using SQL Server 2000 which does not have ROW_NUMBER(), sorry

EDIT2: Since you are storing the results of the query in a temp table you can add an incrementing integer field to that result set and use that as a simulation for the ROW_NUMBER() in order to select the row you need.

EDIT3: Here's a link to an article discussing pagination in SQL Server 2000