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;