Example: Using Nested Compound Statements in a Procedure - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;