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
Last Modified: 27-SEP-00