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.