Controlling the scope of variables in an SQL procedure

Use nested compound statements within an SQL procedure to define the scope of SQL variables. You can reference the variable only within the compound statement in which it was declared and within any nested statements.

Procedure

To control the scope of a variable in an SQL procedure:

  1. Declare the variable within the compound statement in which you want to reference it. Ensure that the variable name is unique within the compound statement, not including any nested statements. You can define variables with the same name in other compound statements in the same SQL procedure.
  2. Reference the variable within that compound statement or any nested statements.
    Recommendation: If multiple variables with the same name exist within an SQL procedure, qualify the variable with the label from the compound statement in which it was declared. Otherwise, you might accidentally reference the wrong variable.

    If the variable name is unqualified and multiple variables with that name exist within the same scope, Db2 uses the variable in the innermost compound statement.

Example

The following example contains three declarations of the variable A. One instance is declared in the outer compound statement, which has the label OUTER1. The other instances are declared in the inner compound statements with the labels INNER1 and INNER2. In the INNER1 compound statement, Db2 presumes that the unqualified references to A in the assignment statement and UPDATE statement refer to the instance of A that is declared in the INNER1 compound statement. To refer to the instance of A that is declared in the OUTER1 compound statement, qualify the variable as OUTER1.A. 

CREATE PROCEDURE P2 ()
         LANGUAGE SQL

 -- Outermost compound statement ------------------------
OUTER1: BEGIN  1 
        DECLARE A INT DEFAULT 100;

        -- Inner compound statement with label INNER1 ---
        INNER1:  BEGIN  2 
                   DECLARE A INT DEFAULT NULL;
                   DECLARE W INT DEFAULT NULL;

                   SET A = A + OUTER1.A;  3 

                   UPDATE T1 SET T1.B = 5
                    WHERE T1.B = A;  4 

                   SET OUTER1.A = 100;  5 

                   SET INNER1.A = 200;  6 
       END INNER1;  7 
       -- End of inner compound statement INNER1 ------

       -- Inner compound statement with label INNER2 ---
       INNER2:  BEGIN  8 
                  DECLARE A INT DEFAULT NULL;
                  DECLARE Z INT DEFAULT NULL;

                  SET A = A + OUTER1.A;

       END INNER2;  9 
       -- End of inner compound statement INNER2 ------
      
      SET OUTER1.A = 100;  10 
     
END OUTER1  11 

The preceding example has the following parts:

  1. The beginning of the outermost compound statement, which has the label OUTER1.
  2. The beginning of the inner compound statement with the label INNER1.
  3. The unqualified variable A refers to INNER1.A.
  4. The unqualified variable A refers to INNER1.A.
  5. OUTER1.A is a valid reference, because this variable is referenced in a nested compound statement.
  6. INNER1.A is a valid reference, because this variable is referenced in the same compound statement in which it is declared. You cannot reference INNER2.A, because this variable is not in the scope of this compound statement.
  7. The end of the inner compound statement with the label INNER1.
  8. The beginning of the inner compound statement with the label INNER2.
  9. The end of the inner compound statement with the label INNER2.
  10. OUTER1.A is a valid reference, because this variable is referenced in the same compound statement in which it is declared. You cannot reference INNER1.A, because this variable is declared in a nested statement and cannot be referenced in the outer statement.
  11. The end of the outermost compound statement, which has the label OUTER1.