Example: Using Nested Compound Statements in a Procedure - Teradata Vantage - Analytics Database

The following example shows nesting of compound statements.

The local variable and cursor name of the outer compound statement labeled L1 are used in the inner compound statement labeled L2.

    CREATE PROCEDURE sp_sample1(IN p_name CHAR(30), INOUT p_amt INTEGER)
    L1: BEGIN 
       DECLARE cvar1, v_amt INTEGER;
       DECLARE cursor1 CURSOR WITHOUT RETURN FOR
           SELECT c1 AS c_c1, c2 AS c_c2 
           FROM temp;
       L2: BEGIN
           DECLARE v_name CHARACTER(30);
           DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' 
              BEGIN
              OPEN cursor1;
              INSERT INTO temp VALUES (v_name, v_amt);
              END;
           SET vName = p_name;
                    -- tab1 table does not exist
           INSERT INTO tab1 VALUES (v_name, v_amt);
           FETCH cursor1 INTO cvar1, p_amt;
       END L2;
    END L1;