MySQL DECLARE ... HANDLER Statement

Summary: in this tutorial, you will learn how to use MySQL handler to handle errors or warnings encountered in stored procedures.

In MySQL, conditions refer to errors, warnings, or exceptional cases that require proper handling.

When a condition arises during the execution of a stored procedure, you should handle it properly, such as exiting or continuing the current code block.

To handle a condition, you declare a handler using the DECLARE ... HANDLER statement.

Introduction to MySQL DECLARE … HANDLER statement

Here is the basic syntax of the DECLARE...HANDLER statement:

DECLARE { EXIT | CONTINUE } HANDLER
    FOR condition_value [, condition_value] ...
    statementCode language: PHP (php)

In this syntax:

DECLARE { EXIT | CONTINUE } HANDLER: This declares a handler, instructing whether it should exit or continue the enclosing stored procedure when a specified condition occurs.
- EXIT: The stored procedure will terminate.
- CONTINUE: The stored procedure will continue execution.
FOR condition_value [, condition_value] ...: This specifies the conditions that activate the handler, and you can specify multiple conditions by separating them with commas.
statement: This statement or block of statements executes when the stored procedure encounters one of the specified conditions.

The condition_value can be one of the following:

mysql_error_code – This is an integer indicating a MySQL error code such as 1051.
SQLWARNING – This is a shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND – This is a shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION – This is a shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
SQLSTATE [VALUE] sqlstate_value – This is a string that indicates an SQLSTATE value, such as '42S01' means “Unknown table”.

SQLSTATE is a five-character that provides information about the result of an SQL operation. An SQLSTATE consists of two parts:

Class Code (First two characters): Indicates the general category of the error.
Subclass Code (Next three characters): Provides more specific information about the error within the general category.

For example, a SQLSTATE code of ’42S02′ indicates a missing table, where ’42’ is the class code for syntax error or access rule violation, and ‘S02’ is the subclass code indicating that the table is not found.

MySQL DECLARE … HANDLER example

First, create a table called users:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(50) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, define a stored procedure that inserts a new user into the users table:

DELIMITER //

CREATE PROCEDURE insert_user(
	IN p_username VARCHAR(50), 
    IN p_email VARCHAR(50)
)
BEGIN
  -- SQLSTATE for unique constraint violation
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    -- Handler actions when a duplicate username is detected
    SELECT 'Error: Duplicate username. Please choose a different username.' AS Message;
  END;

  -- Attempt to insert the user into the table
  INSERT INTO users (username, email) VALUES (p_username, p_email);

  -- If the insertion was successful, display a success message
  SELECT 'User inserted successfully' AS Message;

END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

How it works.

The insert_user() stored procedure accepts two parameters username and email.

In the stored procedure, declare an exit handler that is activated when a unique constraint violation occurs, which is indicated by the SQLSTATE ‘23000’:

DECLARE EXIT HANDLER FOR SQLSTATE '23000'Code language: SQL (Structured Query Language) (sql)

When the error occurs, the stored procedure returns the following message and terminates the execution immediately:

SELECT 'Error: Duplicate username. Please choose a different username.' AS Message;Code language: SQL (Structured Query Language) (sql)

Insert a new row into the users table, if a unique constraint violation occurs, the code within the BEGIN ... END block of the handler will execute:

INSERT INTO users (username, email) VALUES (p_username, p_email);Code language: SQL (Structured Query Language) (sql)

If the insert succeeds, the following line of code will execute:

SELECT 'User inserted successfully' AS Message;Code language: SQL (Structured Query Language) (sql)

Third, insert a new row into the users table by calling the insert_user stored procedure:

CALL insert_user('jane','[email protected]');Code language: SQL (Structured Query Language) (sql)

It returns the following message:

+----------------------------+
| Message                    |
+----------------------------+
| User inserted successfully |
+----------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)Code language: plaintext (plaintext)

If you execute the statement again, it’ll return the following error:

+----------------------------------------------------------------+
| Message                                                        |
+----------------------------------------------------------------+
| Error: Duplicate username. Please choose a different username. |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)Code language: plaintext (plaintext)

Summary

Use MySQL handlers to handle conditions including warnings and errors in stored procedures.
Use the DECLARE...HANDLER statement to declare a handler.

Was this tutorial helpful?