Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 8

A more interesting example:

SET TERM ^ ;
CREATE PROCEDURE ADD_EMP_PROJ (
EMP_NO Smallint,
PROJ_ID Char(5) )
ASBEGIN
BEGIN
INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);
WHEN SQLCODE -530 DO
EXCEPTION unknown_emp_id;
END
SUSPEND;
END^
SET TERM ; ^


This procedure makes use of exceptions to manage errors, MySQL currently doesn't support them but equivalent can be achieved with condition handlers
and variables, see below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `employee`.`add_emp_proj` $$
CREATE PROCEDURE `employee`.`add_emp_proj` (IN empno smallint, IN projid char(5), OUT error char(5))
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 'error';

-- DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK
SET @add_emp_proj = concat('INSERT INTO employee_project (emp_no, proj_id) VALUES (', empno, ',', projid, ')');
PREPARE `add_emp_proj` FROM @add_emp_proj;
EXECUTE `add_emp_proj`;
DEALLOCATE PREPARE `add_emp_proj`;
END $$

DELIMITER ;

there is also a more radical handler commented out.
This procedure shows usage of prepared statements in sps and also usage of a variable to check procedure
execution status, see it in action:

mysql> set @error = 'none';
Query OK, 0 rows affected (0.00 sec)

mysql> select @error from dual;
+--------+
| @error |
+--------+
| none |
+--------+
1 row in set (0.00 sec)

mysql> CALL add_emp_proj(2, '5', @error);
Query OK, 0 rows affected (0.00 sec)

mysql> select @error from dual;
+--------+
| @error |
+--------+
| error |
+--------+
1 row in set (0.00 sec)

mysql>