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;