MySQL Stored Procedure Programming

Introduction to Error Handling

Let’s begin by looking at several examples of stored program error handling.

A Simple First Example

Consider a simple stored procedure that creates a location record, as shown in Example 6-1.

Example 6-1. Simple stored procedure without error handling

CREATE PROCEDURE sp_add_location
         (in_location   VARCHAR(30),
          in_address1   VARCHAR(30),
          in_address2   VARCHAR(30),
          zipcode       VARCHAR(10))
    MODIFIES SQL DATA
BEGIN
   INSERT INTO locations
     (location,address1,address2,zipcode)
    VALUES
     (in_location,in_address1,in_address2,zipcode);
END$$

This procedure works fine when the location does not already exist, as shown in the following output:

    mysql> CALL sp_add_location('Guys place','30 Blakely Drv',
                                'Irvine CA','92618-20');

    Query OK, 1 row affected, 1 warning (0.44 sec)

However, if we try to insert a department that already exists, MySQL raises an error:

    mysql> CALL sp_add_location('Guys place','30 Blakely Drv',
                                'Irvine CA','92618-20');

    ERROR 1062 (23000): Duplicate entry 'Guys place' for key 1

If the stored procedure is called by an external program such as PHP, we could probably get away with leaving this program “as is.” PHP, and other external programs, can detect such error conditions and then take appropriate action. If the stored procedure is called from another stored procedure, however, we risk causing the entire procedure call stack to abort. That may not be what we want.

Since we can anticipate that MySQL error 1062 could be raised by this procedure, we can write code ...