6.7. EXECUTE PROCEDURE

Used forExecuting a stored procedure

Available inDSQL, ESQL, PSQL

Syntax 

   |EXECUTE PROCEDURE procname
   |   [{ <inparam-list | ( <inparam-list> ) }]
   |   [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }]
   | 
   |<inparam-list> ::=
   |  <inparam> [, <inparam> ...]
   | 
   |<outvar-list> ::=
   |  <outvar> [, <outvar> ...]
   | 
   |<outvar> ::= [:]varname

Table 6.20. Arguments for the EXECUTE PROCEDURE Statement Parameters
ArgumentDescription

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

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.

6.7.1. 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.

ⓘ
Note

  • 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.

6.7.2. Examples of EXECUTE PROCEDURE

  1. In PSQL, with optional colons and without optional parentheses:

      |EXECUTE PROCEDURE MakeFullName
  |  :FirstName, :MiddleName, :LastName
  |  RETURNING_VALUES :FullName;

  2. In Firebird’s command-line utility isql, with literal parameters and optional parentheses:

      |EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
    ⓘ
    Note

    In DSQL (eg in isql), RETURNING_VALUES is not used. Any output values are captured by the application and displayed automatically.

  3. A PSQL example with expression parameters and optional parentheses:

      |EXECUTE PROCEDURE MakeFullName
  |  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  |  RETURNING_VALUES (FullName);