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.
