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
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;