Queries That Call Stored Procedures - Firebird

Firebird 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(?);

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics