Stored Procedures

Firebird supports two mechanisms to call stored procedures.

execute procedure MyProc(?,?)

In this example the stored procedure expects to receive data based on the parameters that are being passed. If the parameters are invalid, nothing will be returned.

select * from MyProc(?,?)

In this example the stored procedure expects to generate a result set.

Programs such as Microsoft Excel etc when calling a stored procedure use the following

{[? =] Call MyProc (?,?)}.

The Firebird ODBC driver determines what call to use to execute the stored procedure depending on how the stored procedure was constructed. The key to this is the usage of the word SUSPEND in the stored procedure definition.

If the BLR code for the stored procedure contains if (countSUSPEND == 1) as would be the case using this stored procedure defintion:

create procedure TEST
  as
    begin
    end

Then the ODBC driver will use execute procedure TEST.

If the BLR code for the stored procedure contains if (countSUSPEND > 1) as would be the case in this stored procedure definition:

create procedure "ALL_LANGS"
   returns ("CODE" varchar(5),
         "GRADE" varchar(5),
         "COUNTRY" varchar(15),
         "LANG" varchar(15))
   as
   BEGIN
     "LANG" = null;
     FOR SELECT job_code, job_grade, job_country FROM job
     INTO :code, :grade, :country
     DO
       BEGIN
         FOR SELECT languages FROM show_langs(:code, :grade, :country)
         INTO :lang
           DO
             SUSPEND;
             /* Put nice separators between rows */
             code = '=====';
             grade = '=====';
             country = '===============';
             lang = '==============';
             SUSPEND;
       END
     END

Then the ODBC Driver will use select * from "ALL_LANGS"

For more details of how to do this and for other advanced topics please look at the examples.