community.borland.com

Article #25437: FOR SELECT --- DO Example

Problem:
 When using the keyword "suspend" in an executable procedure, the
       result set will return just one row after the first fetch.  Following
       is an example code to indicate the important items to check
       when using a "for select -- do" form of fetching in order to manipulate
       returning rows based on some criteria...amongst two tables.... 


Solution:
connect employee.gdb;
  set term !!;
  create procedure count_sel (mydt date)
   returns (jcode char(5),
           jgrade integer,
           fcnt   integer)
  as                      
 begin
    for select distinct job_code from job into :jcode
    do
      begin
        select count(job_grade) from employee
        where job_code = :jcode
        and (hire_date <:mydt)
        into :jgrade;

        select count(first_name) from employee
        where
        job_code=:jcode
        and (hire_date > :mydt)
        into :fcnt;
        suspend;
      end
  end!!
  set term ;!!
  ===================================================
execute procedure count_sel("10-MAR-1990")
JCODE       JGRADE        FCNT
====== =========== ====
Accnt            0           0
  ===================================================
select * from count_sel("2-MAR-1988");

JCODE       JGRADE        FCNT
====== =========== ===========

Accnt            0           0
Admin            0           4
CEO              0           1
CFO              0           1
Dir              0           1
  =================================================  

Last Modified: 26-OCT-00