Question:
I’m getting the first “page” of data from a table and I’m wondering about getting the next page…

I’m using the query:

SELECT TOP 50 ID, Name, Address, Phone, RankFROM Person, FileWHERE Person.RankID = File.RankID AND Name LIKE 'R%'ORDER BY Name ASC

I would like to then be able to retrieve the next 50 records, preferably without using cursors.

Is there any way to do this?

PS. I am using SQL Server 7.0.

Answer:
Well, the normal way of traversing a table without cursors would be to have a line in the WHERE clause that limits the rows returned to ones that you haven’t seen yet. In your example, let’s say that the last ID returned was 50; you would addwhere id > 50 to obtain your next result set.

The problem is how to do this programmatically. If you are sending the result set back to a front end where you can then capture the last row and modify your SQL dynamically, you are in luck.

However, for the purposes of this answer, I will stick to pure T-SQL solutions.

If I were processing one row at a time, I could do the following (using pubs..authors as an example):

declare @id char(30) select @id = min (au_id)from authorsselect * from authorswhere au_id = @idwhile @id is not nullbegin     print 'Do whatever processing you need to for the row'     select @id     select @id = min(au_id) from authors where au_id > @idend

However, it is illegal to say “select @id = top 3 au_id …” etc., so there is no easy way to jump by units of 50.