Sybase IQ Pagination

问题:

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.

回答1:

I don't 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)

回答2:

Sybase IQ and Sybase SQL Anywhere share the same query execution engine and (mostly) SQL syntax, so you can generally use SQL Anywhere syntax. Try this:

select top (endIndex-startIndex) start at startIndex from <query>

I'm not sure if you can use an expression in the top clause, so you may have to create a string and use execute immediate.

See http://dcx.sybase.com/index.html#1201/en/dbreference/select-statement.html