Record nagivation in a stored procedure? 1

[redlam]

Hiya.
Is there any tricky way to avoid a cursor for the following:
We are using ASP to display the results of a stored procedure. We are attempting to limit the number of records returned on each page. I would like to be able to use a select statement using TOP and possibly some sort of 'NEXT' operator to acheive these results. Anyone have any brilliant ideas?
Thanks!!!

 

[guljar]

In two way you get the solutions from wherever you called the stored procedure.
In any case you have to pass the starting row no say S and the no of rows N and you are trying to retrieve data from Table X. (You should have a ID field must; i.e. a PK)
Way 1: if your starting record is 1 then just
select top N * from X
if starting is greater than 1 then
select top N * from X where ID not in(select Top S-1 ID from X)
Way 2: This is a little complex as you have to create a temp table runtime and insert the required data for the first fetch then for next fetches you can select data joining the data of the temp table and the main table.
Way one is simple but costly for fetch 1+N i.e. for selection first few records it is simple but for next fetch as there is a subquery it is always costly.
For way 2 I have to write details. IF you feel interest write me.

 

[guljar]

In two way you get the solutions from wherever you called the stored procedure.
In any case you have to pass the starting row no say S and the no of rows N and you are trying to retrieve data from Table X. (You should have a ID field must; i.e. a PK)
Way 1: if your starting record is 1 then just
select top N * from X
if starting is greater than 1 then
select top N * from X where ID not in(select Top S-1 ID from X)
Way 2: This is a little complex as you have to create a temp table runtime and insert the required data for the first fetch then for next fetches you can select data joining the data of the temp table and the main table.
Way one is simple but costly for fetch 1+N i.e. for selection first few records it is simple but for next fetch as there is a subquery it is always costly.
For way 2 I will need to write details. IF you feel interest write me.

 

[redlam]

thank you guljar. i'll give it a go and email you if i need help.
thanks again.