How to set up record paging with a stored proc?

RhythmAddict · #1 (**permalink**) 09-30-04, 10:21

Hi everyone...I've got an ASP page that has an oracle stored proc in it. The problem is im paging records and since I have to run the entire query in the SP even though I'm only viewing say 15 at a time. This is coming at a very large costs because of the sheer amount of total records (20k +??)
I've found this article:
http://www.15seconds.com/issue/010308.htm

On writing a stored proc to do record paging on SQL server....Can anyone give me an example or a link with further info on settnig up record paging with Oracle instead? This would get me out of a huge jam...

Thank you in advance

andrewst · #2 (**permalink**) 09-30-04, 12:15

Modify the procedure to take 2 extra IN parameters:
p_from in integer,
p_to in integer

These can then be used in the query like this:

Code:

select empno, ename, rn from
( select empno, ename, rownum rn from
  ( select empno, ename from emp
    order by empno
  )
  where rownum <= p_to
)
where rn >= p_from;

Then call it from your ASP page with p_from and p_to values like 1 and 15, 15 and 30, 31 and 45 etc.