Creating Nested and Recursive Procedures

Go Up to The Procedure Body

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 can be restricted to fewer than 1,000 levels by memory and stack limitations of the server.

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

The following C code demonstrates how a host-language program would call FACTORIAL:

. . .
printf('\nCalculate factorial for what value? ');
scanf('%d', &pnum);
EXEC SQL
EXECUTE PROCEDURE FACTORIAL :pnum RETURNING_VALUES
:pfact;
printf('%d factorial is %d.\n', pnum, pfact);
. . .

Recursion nesting restrictions would not allow this procedure to calculate
factorials for numbers greater than 1,001. Arithmetic overflow, however, occurs for much smaller numbers.