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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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;