MySQL Stored Procedure Programming

Dealing with Errors

When an error occurs in a stored program, the default behavior of MySQL is to terminate execution of the program and pass the error out to the calling program. If you need a different kind of response to an error, you create an error handler that defines the way in which the stored program should respond to one or more error conditions.

The following are two relatively common scenarios that call for the definition of error handlers:

• If you think that an embedded SQL statement might return no rows, or you need to fetch all the rows from a SELECT statement using a cursor, a NOT FOUND error handler will prevent the stored program from terminating prematurely.

• If you think that a SQL statement might return an error (a constraint violation, for instance), you may need to create a handler to prevent program termination. The handler will, instead, allow you to process the error and continue program execution.

Chapter 6 describes in detail how to use error handlers. An example of using a NOT FOUND error handler with a cursor is shown in the next section.