How to Use Handler for Error in MySQL Stored Procedures

In MySQL stored procedures, you use DECLARE ... HANDLER to catch errors or warnings and handle them gracefully. This lets you define actions like setting variables or continuing execution when an error occurs.

Syntax

The DECLARE ... HANDLER statement defines how to handle specific conditions like errors or warnings inside stored procedures. It has three main parts:

• Handler type: CONTINUE or EXIT to decide if the procedure continues or stops after the handler runs.
• Condition value: The error or warning to catch, such as SQLEXCEPTION (any error), SQLWARNING, or specific error codes.
• Handler code: The statements to execute when the condition happens.

sql

DECLARE handler_type HANDLER FOR condition_value statement;

Example

This example shows a stored procedure that tries to insert a row into a table. If an error occurs (like a duplicate key), the handler catches it and sets a variable instead of stopping the procedure.

sql

DELIMITER $$
CREATE PROCEDURE InsertUser(IN user_id INT, IN user_name VARCHAR(50), OUT msg VARCHAR(100))
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SET msg = 'Error: Could not insert user.';
  END;

  INSERT INTO users(id, name) VALUES (user_id, user_name);
  SET msg = 'User inserted successfully.';
END$$
DELIMITER ;

Common Pitfalls

Common mistakes when using handlers include:

• Not specifying CONTINUE or EXIT, which defaults to EXIT and stops the procedure.
• Using handlers outside stored procedures, which is not allowed.
• Not handling specific errors, causing unexpected procedure termination.

Always test your handlers to ensure they behave as expected.

sql

/* Wrong: Missing CONTINUE or EXIT, defaults to EXIT */
DECLARE HANDLER FOR SQLEXCEPTION SET @msg = 'Error';

/* Right: Explicit CONTINUE to keep running */
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @msg = 'Error';

Quick Reference

Part	Description	Example
handler_type	CONTINUE or EXIT to control flow after handler	DECLARE CONTINUE HANDLER
condition_value	Error or warning to catch (e.g., SQLEXCEPTION)	FOR SQLEXCEPTION
statement	Code to run when error occurs	SET msg = 'Error occurred';

Key Takeaways

Use DECLARE ... HANDLER inside stored procedures to catch and manage errors.

Choose CONTINUE to keep running after an error or EXIT to stop the procedure.

Handlers must be declared before other statements in the procedure body.

Test handlers to ensure they handle errors as expected without stopping execution.

Handlers cannot be used outside stored procedures.