Queries That Call Stored Procedures - Firebird
Interview Questions
All Tutorials
Job SearchFirebird supports two styles of stored procedures: executable and selectable.
Executable Procedures
In DSQL, EXECUTE PROCEDURE executes (calls) an executable stored procedure—that is, a stored procedure designed to perform some operations on the server, optionally returning a single line of one or more return values. The statement for executing such a procedure has the following general format:
EXECUTE PROCEDURE procedure-name [(<list of input values>)]The following simple example illustrates calling an executable procedure that accepts two input arguments, performs some operations on the server, and exits:
EXECUTE PROCEDURE DO_IT(49, '25-DEC-2004');In applications, it is more powerful to use parameters (see the section “Using Parameters”) in query statements that execute stored procedures, for example:
EXECUTE PROCEDURE DO_IT(:IKEY, :REPORT_DATE);or
EXECUTE PROCEDURE DO_IT(?, ?);Selectable Procedures
A selectable stored procedure is capable of returning a multi-row set of data in response to a specialized SELECT statement form, as follows:
SELECT <list of output columns> FROM procedure-name [(<list of input values>)] [WHERE <search predicates>] [ORDER BY <list drawn from output columns>]In the following PSQL fragment, a stored procedure is defined to accept a single key as an input argument and return a set of rows. The RETURNS clause defines the output set:
CREATE PROCEDURE GET_COFFEE_TABLE (IKEY INTEGER) RETURNS ( BRAND_ID INTEGER, VARIETY_NAME VARCHAR(40), COUNTRY_OF_ORIGIN VARCHAR(30)) AS ..........The application selects the output set from the stored procedure as follows:
SELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(5002);This is the same example, with the input argument parameterized:
SELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(:IKEY);/* Delphi */or
SELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(?);
