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, Rank
FROM Person, File
WHERE 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.
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 add
where 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)
select * from authors
where au_id = @id
while @id is not null
print 'Do whatever processing you need to for the row'
select @id = min(au_id) from authors where au_id > @id
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.