EXECUTE PROCEDURE

Table of Contents

“Executable” Stored Procedure

Used for: Executing a stored procedure

Available in: DSQL, ESQL, PSQL

Syntax:

EXECUTE PROCEDURE procname
   [<inparam> [, <inparam> ...]] | [(<inparam> [, <inparam> ...])]
   [RETURNING_VALUES <outvar> [, <outvar> ...] | (<outvar> [, <outvar> ...])]

<outvar> ::= [:]varname

Editor's note :: Notice I added the non-parenthesised option for return parameters, after an exchange with Dmitry Y.

Table 6.16. Arguments for the EXECUTE PROCEDURE Statement Parameters

Argument	Description
procname	Name of the stored procedure
inparam	An expression evaluating to the declared data type of an input parameter
varname	A PSQL variable to receive the return value

Description: Executes an executable stored procedure, taking a list of one or more input parameters, if they are defined for the procedure, and returning a one-row set of output values, if they are defined for the procedure.

“Executable” Stored Procedure

The EXECUTE PROCEDURE statement is most commonly used to invoke the style of stored procedure that is written to perform some data-modifying task at the server side—those that do not contain any SUSPEND statements in their code. They can be designed to return a result set, consisting of only one row, which is usually passed, via a set of RETURNING_VALUES() variables, to another stored procedure that calls it. Client interfaces usually have an API wrapper that can retrieve the output values into a single-row buffer when calling EXECUTE PROCEDURE in DSQL.

Invoking the other style of stored procedure—a “selectable” one—is possible with EXECUTE PROCEDURE but it returns only the first row of an output set which is almost surely designed to be multi-row. Selectable stored procedures are designed to be invoked by a SELECT statement, producing output that behaves like a virtual table.

	Notes
In PSQL and DSQL, input parameters may be any expression that resolves to the expected type. Although parentheses are not required after the name of the stored procedure to enclose the input parameters, their use is recommended for the sake of good housekeeping. Where output parameters have been defined in a procedure, the RETURNING_VALUES clause can be used in PSQL to retrieve them into a list of previously declared variables that conforms in sequence, data type and number with the defined output parameters. The list of RETURNING_VALUES may be optionally enclosed in parentheses and their use is recommended. When DSQL applications call EXECUTE PROCEDURE using the Firebird API or some form of wrapper for it, a buffer is prepared to receive the output row and the RETURNING_VALUES clause is not used.

Notes

In PSQL and DSQL, input parameters may be any expression that resolves to the expected type.
Although parentheses are not required after the name of the stored procedure to enclose the input parameters, their use is recommended for the sake of good housekeeping.
Where output parameters have been defined in a procedure, the RETURNING_VALUES clause can be used in PSQL to retrieve them into a list of previously declared variables that conforms in sequence, data type and number with the defined output parameters.
The list of RETURNING_VALUES may be optionally enclosed in parentheses and their use is recommended.
When DSQL applications call EXECUTE PROCEDURE using the Firebird API or some form of wrapper for it, a buffer is prepared to receive the output row and the RETURNING_VALUES clause is not used.

Examples:

In PSQL, with optional colons and without optional parentheses:
EXECUTE PROCEDURE MakeFullName
  :FirstName, :MiddleName, :LastName
  RETURNING_VALUES :FullName;
          
In Firebird's command-line utility isql, with literal parameters and optional parentheses:
EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
          
Note: In isql, RETURNING_VALUES is not used. Any output values are captured by the application and displayed automatically.

A PSQL example with expression parameters and optional parentheses:
EXECUTE PROCEDURE MakeFullName
  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  RETURNING_VALUES (FullName);
          