Error handling in procedures in MySQL

Introduction

Error handling in procedures helps you catch and manage problems during database operations so your program can respond properly instead of stopping unexpectedly.

When you want to handle division by zero errors in calculations inside a procedure.

When inserting data that might violate unique constraints and you want to handle the error gracefully.

When calling external procedures that might fail and you want to log the error or take alternative actions.

When you want to provide custom error messages to users instead of default database errors.

Syntax

MySQL

DECLARE handler_type HANDLER FOR condition_value statement;

-- Example:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  -- error handling code here
END;

handler_type can be CONTINUE, EXIT, or UNDO (UNDO is rarely used).

condition_value can be specific error codes, SQLSTATE values, or general conditions like SQLEXCEPTION.

Examples

This handler catches any SQL exception and continues execution after setting an error message.

MySQL

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  SET @error_message = 'An error occurred';
END;

This handler catches duplicate entry errors and exits the procedure after setting a message.

MySQL

DECLARE EXIT HANDLER FOR 1062
BEGIN
  -- 1062 is duplicate entry error
  SET @error_message = 'Duplicate entry found';
END;

This handler is useful when fetching rows in a loop and no more rows are found.

MySQL

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
  SET @no_more_rows = TRUE;
END;

Sample Program

This procedure tries to insert a user name into the users table. If any error happens, it shows a friendly error message instead of stopping abruptly.

MySQL

DELIMITER $$
CREATE PROCEDURE InsertUser(IN username VARCHAR(50))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Handle any SQL error
    SELECT 'Error: Could not insert user.' AS message;
  END;

  INSERT INTO users (name) VALUES (username);
  SELECT 'User inserted successfully.' AS message;
END$$
DELIMITER ;

-- Call the procedure with a username
CALL InsertUser('Alice');

Important Notes

Always declare handlers at the start of the BEGIN...END block in your procedure.

Use EXIT handler to stop the procedure after an error, CONTINUE to keep going.

You can declare multiple handlers for different error types.

Summary

Error handlers catch problems during procedure execution to prevent crashes.

Use DECLARE HANDLER with CONTINUE or EXIT to control error flow.

Handlers help you give clear messages or take alternative actions on errors.