How to Use Nested Labels in DB2 SQL Procedure

SQL procedures you can create for a specific purpose, which is repetitive. The writing of structural-SQL-procedure is an art. After you write, for its maintenance and readability Label plays a prime role. Because the developer who is modifying it must understand the purpose. Else he may change it incorrectly.

SQL Procedure Label Rules.

• A label is not executable. You can write n number of labels
• You can write nested labels – a label within another label
• Syntax to write is – LABEL1:
• After the END, it is good practice to give label name, but this is optional

Example SQL Procedure with Nested Labels.

CREATE PROCEDURE show_label (OUT p_WorkerID INT)
    SPECIFIC show_label
    LANGUAGE SQL
-- Procedure logic
sl1: BEGIN
-- Declare variables 
   DECLARE v_ID INT;                                                 -- (1)
   sl2: BEGIN
       DECLARE v_ID INT;                                             -- (2)
       SET sl1.v_ID = 1;                                             -- (3)
       SET sl2.v_ID = 2;                                             -- (4)
       SET v_ID = 3;                                                 -- (5)
       SET p_WorkerID = sl2.v_ID;                                    -- (6)
   END;
END sl1

Takeaways.

• Variables you declared in the label structure, to reference those follow the below syntax: label.variable_name.
• When you don’t put the label name, by default, it assigns the current label name in front of the variable name.

References

• IBM GOTO statement in SQL procedure with Labels

Keep Reading