Sybase IQ Pagniation

Question

Hi there,

I wonder if anyone has a solution to the following requirement. I have a stored procedure which returns a result set of for example 1000 rows. Now I need to limit this to 100 rows at a time. So I will pass in a start and end index value and I only want the records between the start index rowcount and the end index rowcount

So for example my stored procedure call signature looks like this:-

stp_mystoredproc(startIndex INTEGER, endIndex INTEGER)

So if I set "startIndex" = 100 and "endIndex" = 200 then I want the stored procedure to return the records in rows 100 to 200 out of the total reset set of 1000.

My first attempt is put the result set in a temp table with an identity column then select based on the identity the range I need but this is somewhat slow. I know Oracle supports pagination so you can page through your result set. Anyone know if Sybase IQ (v12.6 or v12.7) supports something similar?

The end goal is to page through the entire result set (1000 records) but in 100 row pages at a time.

Many thanks in advance,

Kevin

Answer 1

Dont know sybase. But maybe you could do something like this

myproc(@count int, @lastid int)

select top @count * from MyTabel where id > @lastid order by id

first call exec myproc(100,0) gives you something like 3 appels 4 banana .. .. .. 346 potatto

next call exec myproc myproc(100,346)