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

Teradata Database SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Release Number
15.10
Published
December 2015
Content Type
Programming Reference
Publication ID
B035-1144-151K
Language
English (United States)
Last Update
2018-06-05

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;