EXECUTE PROCEDURE (Procedures)
Go Up to Procedures and Triggers
Executes a stored procedure. Available in triggers and stored procedures.
EXECUTE PROCEDURE name [:<param> [, :<param> …]]
[RETURNING_VALUES :<param> [, :<param> …]];
| Argument | Description |
|---|---|
|
<name> |
Name of the procedure being executed. Must have been previously defined to the database with |
|
[<param> [, <param> …]] |
List of input parameters, if the procedure requires them
|
|
[ |
List of output parameters, if the procedure returns values; precede each with a colon, except |
Description: A stored procedure can itself execute a stored procedure. Each time a stored procedure calls another procedure, the call is said to be nested because it occurs in the context of a previous and still active call to the first procedure. A stored procedure called by another stored procedure is known as a nested procedure.
If a procedure calls itself, it is recursive. Recursive procedures are useful for tasks that involve repetitive steps. Each invocation of a procedure is referred to as an instance, since each procedure call is a separate entity that performs as if called from an application, reserving memory and stack space as required to perform its tasks.
- Note: Stored procedures can be nested up to 1,000 levels deep. This limitation helps to prevent infinite loops that can occur when a recursive procedure provides no absolute terminating condition. Nested procedure calls may be restricted to fewer than 1,000 levels by memory and stack limitations of the server.
Example: The following example illustrates a recursive procedure, FACTORIAL, which calculates factorials. The procedure calls itself recursively to calculate the
factorial of NUM, the input parameter.
CREATE PROCEDURE FACTORIAL (NUM INT)
RETURNS (N_FACTORIAL DOUBLE PRECISION)
AS
DECLARE VARIABLE NUM_LESS_ONE INT;
BEGIN
IF (NUM = 1) THEN
BEGIN /**** Base case: 1 factorial is 1 ****/
N_FACTORIAL = 1;
EXIT;
END
ELSE
BEGIN
/**** Recursion: num factorial = num * (num-1) factorial ****/
NUM_LESS_ONE = NUM - 1;
EXECUTE PROCEDURE FACTORIAL NUM_LESS_ONE
RETURNING_VALUES N_FACTORIAL;
N_FACTORIAL = N_FACTORIAL * NUM;
EXIT;
END
END;
See Also
For more information on executing procedures, see EXECUTE PROCEDURE.