Recursive Stored Procedures | Teradata Vantage - Recursive Stored Procedures - Advanced SQL Engine - Teradata Database

A stored procedure can be recursive, referencing itself directly or indirectly. That is, the stored procedure body can contain a CALL statement invoking the procedure being defined. Such CALL statements can also be nested.

When the stored procedure being created directly references or invokes itself, the procedure is created with an SPL compilation warning (not an error) because the referenced object (the procedure) does not exist.

No specific limit exists on the level of recursion, but the stored procedure nesting limit of 15 applies. The limit is further reduced if there are any open cursors.

Mutual Recursion

You can also create mutually recursive stored procedures, that is, procedures invoking each other in a stored procedure body. This is an indirect recursion. An SPL compilation warning is reported when an attempt is made to create either of the procedures because the referenced procedure does not exist.

This warning can be avoided by first creating one stored procedure without the CALL statement to the other procedure, then creating the second stored procedure, and then replacing the first procedure with a definition that includes the CALL to the second procedure.

Chain Recursion

You can extend the mutual recursion process to have a recursion chain through multiple procedures (A calls B, B calls C, and C calls A).

Examples

The first example illustrates the creation of a stored procedure that directly references itself. The stored procedure is created with a compilation warning because the procedure being invoked by the CALL statement does not exist at compilation time.

The second example illustrates the creation of a stored procedure that entails mutual recursion and avoids any compilation warnings. This is useful for creating a new stored procedure or for changing the parameters of an existing procedure.

Example: Recursion

Assume that the table named Employee exists.

CREATE PROCEDURE spCall(INOUT empcode INTEGER,
                        INOUT basic DECIMAL (6, 2))
BEGIN
   IF (empcode < 1005) THEN
      SELECT empbasic INTO basic FROM Employee
          WHERE empcode = empcode ;
      INSERT Temptab(empcode, basic);
      SET empcode = empcode + 1;
      CALL spCall(empcode, basic);
   END IF;
 
   IF (empcode = 1005) THEN
      SET empcode = empcode - 1;
      SELECT max(empbasic) INTO basic from Temptab;
   END IF;
END;

When the stored procedure is compiled, the following compilation warning appears, and the procedure spCall is created successfully.

SPL5000:W(L8), E(3807):Table/view/trigger/procedure ‘spCall’ does not    exist.

Assume that the stored procedure spCall is invoked the first time as spCall (1001, basic (title 'maximum'));.

As the condition in the first IF statement evaluates to true for the values 1001, 1002, 1003, and 1004 passed as the argument for the parameter empcode, the stored procedure invokes itself four times.

Example: Mutual Recursion

Assume that the user U1 is creating the stored procedures. The creator is not the immediate owner of the stored procedures, because both Sp1 and Sp2 are created in the db1 database.

1. Create the first stored procedure Sp1 without recursion.

   CREATE PROCEDURE db1.Sp1(INOUT p1 INTEGER)
   BEGIN
   END;

2. Create the second procedure Sp2 that references the existing procedure db1.Sp1.

   CREATE PROCEDURE db1.Sp2(INOUT p1 INTEGER)
   BEGIN
      IF (p1 > 0) THEN
         CALL db1.Sp1(p1- 1);
      END IF;
   END;

3. Replace the stored procedure Sp1 with one that references Sp2.

   REPLACE PROCEDURE db1.Sp1(INOUT p1 INTEGER)
   BEGIN
      IF (p1 > 0 ) THEN
         CALL db1.Sp2(p1 - 1);
      END IF;
   END;

Related Topics

For more information about mutual recursion, see Example: Mutual Recursion.