Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Oct 6, 2000

Getting the Next Set of Results

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, 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.

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 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)
from 
authors

select * from authors
where au_id = @id

while @id is not null
begin
     print 'Do whatever processing you need to for the row'
     select @id
     select @id = min(au_id) from authors where au_id > @id
end
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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date