grab middle 100 records

[bluebytez]

Hi, I am trying to think of a solution to this problem.
Let's say I have 10,000 records in my database and my web application displays the records in pages ( 100 records per page ). Let's say I would like to navigate to page 3, how can I write a stored procedure for Sybase ASE to return the correct 100 records to me ?
I can achieve this in MS SQL Server as it supports SELECT TOP 100, but I don't think there is an equivalent to SELECT TOP in sybase, is there ?
I hope someone can give me some pointers, Thanks !

 

[mikewoodhouse]

Look at ROWCOUNT, which limits the number of rows returned. If you're looking for a set of (say) 100 rows after the first, you'll need a way of excluding the rows already returned.

e.g.

SET ROWCOUNT 100


HTH,

Mike

 

[bluebytez]

Thanks, Mike.
ROWCOUNT work wonders!

 

[Guest_imported]

Set rowcount works, but another way is to use a cursor.
e.g.

use pubs2
go
declare my_authors_cursor cursor for
select * from authors
go
set cursor rows 10 for my_authors_cursor

open my_authors_cursor
fetch my_authors_cursor

close my_authors_cursor
deallocate cursor my_authors_cursor