Assume you want to create an SQL stored procedure named NewProc that you can use to add new employees to the Employee table and retrieve the department name of the department to which the employee belongs. You can also report an error, in case the row that you are trying to insert already exists, and handle that error condition.
The following SQL stored procedure definition includes nested, labeled compound statements. The compound statement labeled L3 is nested within the outer compound statement L1. Note that the compound statement labeled L2 is the handler action clause of the condition handler.
This SQL stored procedure defines parameters that must be filled in each time it is called (executed).
CREATE PROCEDURE NewProc (IN name CHAR(12), IN num INTEGER, IN dept INTEGER, OUT dname CHAR(10), INOUT p1 VARCHAR(30)) L1: BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE value '23505' L2: BEGIN SET p1='Duplicate Row' ; END L2; L3: BEGIN INSERT INTO Employee (EmpName, EmpNo, DeptNo) VALUES (name, num, dept) ; SELECT DeptName INTO dname FROM Department WHERE DeptNo = dept; IF SQLCODE <> 0 THEN LEAVE L3; ... END L3 ; END L1 ;