Retrieving the first n records from a result set

By: Borland Staff

Abstract: It can be accomplished using a stored provedure

Problem:
There needs to be a way to retrieve a fixed number of records 
from a result set using SQL.

Solution:
One way to do this is to create a stored procedure that performs 
the query and keeps track of how many records it has fetched. 
After the limit has been reached, the procedure should end.

Here's a procedure that returns the employee number, first 
name, and last name from the records in the employee table.  
The parameter rows specifies how many records the 
procedure returns, starting with those who have last names 
at the beginning of the alphabet, or the whole result set, 
whichever is smaller.

create procedure getemp ( rows integer )
returns (emp_no smallint, firstname varchar(15), lastname varchar(20))
as
begin
   if (rows < 1) then exit;
   for select emp_no, first_name, last_name from employee
      order by Last_name 
      into :emp_no, :firstname, :lastname
   do
   begin
      suspend;
      rows = rows - 1;
      if (rows < 1) then exit;
   end
end


Server Response from: ETNASC04