15.10 - Example: Creating an SQL Procedure that Specifies Recursion - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The following CREATE PROCEDURE definition specifies recursion.

     CREATE PROCEDURE rqspl(
       OUT rowcnt INTEGER)
     BEGIN
         DECLARE target1 INTEGER DEFAULT 0;
         DECLARE target2 INTEGER DEFAULT 0;
         DECLARE target3 INTEGER DEFAULT 0;
         DECLARE finalcount INTEGER DEFAULT 0;
         DECLARE pscursor SCROLL CURSOR FOR
         WITH RECURSIVE RQ(x,y,depth) AS
         (
          SELECT a1, b1, 0
 
          FROM t1
          UNION ALL
          SELECT a1, y, depth+1
 
          FROM RQ,t1
 
          WHERE y = a1
 
          AND   depth < 10
          )
         SELECT *
 
         FROM RQ;
         OPEN pscursor;
         FETCH pscursor INTO target1, target2, target3;
         -- if there is no row from the cursor, 
         -- SQLCODE=7632, SQLSTATE=02000
         WHILE (SQLCODE <> 7632)
         DO
            FETCH pscursor INTO target1, target2, target3;
            SET finalcount = finalcount+1;
         END WHILE;
         CLOSE pscursor;
         SET rowcnt = finalcount;
     END;