Example

In the following statement, the parameters rating and v_empno are passed in to the procedure. The time in service is returned as a date duration in output parameter return_parm. If the time in service with the company is less then 6 months, the GOTO statement transfers control to the end of the procedure and new_salary is left unchanged.

CREATE PROCEDURE adjust_salary
  (IN v_empno CHAR(6),
   IN rating INTEGER,
   OUT return_parm DECIMAL(8,2))
  LANGUAGE SQL
  MODIFIES SQL DATA 
    BEGIN
       DECLARE new_salary DECIMAL(9,2);
       DECLARE service DECIMAL(8,2);
            SELECT salary, CURRENT_DATE - hiredate
               INTO new_salary, service
               FROM employee
               WHERE empno = v_empno;
            IF service < 600
              THEN GOTO exit1;
            END IF;
            IF rating = 1
             THEN SET new_salary = new_salary + (new_salary * .10);
             ELSEIF rating = 2
             THEN SET new_salary = new_salary + (new_salary * .05);
            END IF;
            UPDATE employee
               SET salary = new_salary
               WHERE empno = v_empno;

       EXIT1: SET return_parm = service;
     END