EXECUTE PROCEDURE
Go Up to Statement and Function Reference (Language Reference Guide)
Calls a stored procedure. Available in gpre, DSQL, and isql.
SQL form:
EXECUTE PROCEDURE [TRANSACTION transaction]
name [:param [[INDICATOR]:indicator]]
[, :param [[INDICATOR]:indicator] …]
[RETURNING_VALUES :param [[INDICATOR]:indicator]
[, :param [[INDICATOR]:indicator] …]];
DSQL form:
EXECUTE PROCEDURE name [param [, param …]]
[RETURNING_VALUES param [, param …]]
isql form:
EXECUTE PROCEDURE name [param [, param …]]
| Argument | Description |
|---|---|
|
|
Specifies the transaction under which execution occurs |
|
<name> |
Name of an existing stored procedure in the database |
|
<param> |
Input or output parameter; can be a host variable or a constant |
|
|
Host variable which takes the values of an output parameter |
|
[ |
Host variable for indicating |
Description: EXECUTE PROCEDURE calls the specified stored procedure. If the procedure requires input parameters, they are passed as host-language variables or as constants. If a procedure returns output parameters to a SQL program, host variables must be supplied in the RETURNING_VALUES clause to hold the values returned.
In isql, do not use the RETURN clause or specify output parameters. isql will automatically display return values.
In DSQL, an EXECUTE PROCEDURE statement requires an input descriptor area if it has input parameters and an output descriptor area if it has output parameters.
In embedded SQL, input parameters and return values may have associated indicator variables for tracking NULL values. Indicator variables are integer values that indicate unknown or NULL values of return values.
An indicator variable that is less than zero indicates that the parameter is unknown or NULL. An indicator variable that is zero or greater indicates that the associated parameter is known and not NULL.
Examples: The following embedded SQL statement demonstrates how the executable procedure, DEPT_BUDGET, is called from embedded SQL with literal parameters:
EXEC SQL
EXECUTE PROCEDURE DEPT_BUDGET 100 RETURNING_VALUES :sumb;
The next embedded SQL statement calls the same procedure using a host variable instead of a literal as the input parameter:
EXEC SQL
EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;