EXECUTE PROCEDURE

`EXECUTE PROCEDURE`

Available in: DSQL, ESQL, PSQL

Changed in: 1.5

Description

Executes a stored procedure. In Firebird 1.0.x as well as in InterBase, any input parameters for the SP must be supplied as literals, host language variables (in ESQL) or local variables (in PSQL). In Firebird 1.5 and above, input parameters may also be (compound) expressions, except in static ESQL.

Syntax

 EXECUTE PROCEDURE procname
    [TRANSACTION transaction]
    [<in_item> [, <in_item> ...]]
    [RETURNING_VALUES <out_item> [, <out_item> ...]]

 <in_item>  ::= <inparam> [<nullind>]
 <out_item> ::= <outvar> [<nullind>]
 <inparam>  ::= an expression evaluating to the declared parameter type
 <outvar>   ::= a host language or PSQL variable to receive the return value
 <nullind>  ::= [INDICATOR]:host_lang_intvar

Notes

TRANSACTION clauses are not supported in PSQL.
Expression parameters are not supported in static ESQL, and not in Firebird versions below 1.5.
NULL indicators are only valid in ESQL code. They must be host language variables of type integer.
In ESQL, variable names used as parameters or outvars must be preceded by a colon (":"). In PSQL the colon is generally optional, but forbidden for the trigger context variables OLD and NEW.

Examples

In PSQL (with optional colons):

 execute procedure MakeFullName
    :FirstName, :Middlename, :LastName
    returning_values :FullName;

The same call in ESQL (with obligatory colons):

 exec sql
    execute procedure MakeFullName
       :FirstName, :Middlename, :LastName
       returning_values :FullName;

...and in Firebird's command-line utility isql (with literal parameters):

 execute procedure MakeFullName
    'J', 'Edgar', 'Hoover';

Note: In isql, don't use RETURNING_VALUES. Any output values are shown automatically.

Finally, a PSQL example with expression parameters, only possible in Firebird 1.5 and up:

 execute procedure MakeFullName
    'Mr./Mrs. ' || FirstName, Middlename, upper(LastName)
    returning_values FullName;

Search

German

Follow IBExpertise on:

edit SideBar

the database experts

EXECUTE PROCEDURE

EXECUTE PROCEDURE

Search

`EXECUTE PROCEDURE`