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)
-- Procedure logic
-- Declare variables
DECLARE v_ID INT; -- (1)
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)
- 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.