CODEWITHSUNDEEP
sql-serversql-server-2000pagination
Andy Jarrett
Andy Jarrett

Reputation: 873

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 takes the returned resultset and only gives me rows X to Y ?

Upvotes: 1

Views: 669

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 2

Tony
Tony

Reputation: 10327

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

Upvotes: 1

BvdVen
BvdVen

Reputation: 2961

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

Upvotes: 1

Related Questions