DB2: Nested Labels in 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.

Advertisements

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

Keep Reading

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.