Example

The following SQL procedure accepts two IN parameters: an employee number and an employee rating. Depending on the value of rating, the employee table is updated with new values in the salary and bonus columns.

CREATE PROCEDURE UPDATE_SALARY_IF
   (IN employee_number CHAR(6), INOUT rating SMALLINT)
   LANGUAGE SQL
   MODIFIES SQL DATA
   BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
         SET rating = -1;
      IF rating = 1
         THEN UPDATE employee
         SET salary = salary * 1.10, bonus = 1000
         WHERE empno = employee_number;
      ELSEIF rating = 2
         THEN UPDATE employee
         SET salary = salary * 1.05, bonus = 500
         WHERE empno = employee_number;
      ELSE UPDATE employee
         SET salary = salary * 1.03, bonus = 0
         WHERE empno = employee_number;
      END IF;
   END