SQL Error Handling — Catching and Responding to SQL Failures: BEGIN...HANDLER, SIGNAL

The office attempts to add a repeated roll number. Silently continuing could make the application claim a student was saved when the insert failed.

Database error handling determines how a program responds when an operation fails. A handler must match the intended condition and leave the data in an understood state.

MySQL DECLARE HANDLER associates conditions with a response. CONTINUE resumes after handling; EXIT leaves the declaring compound block. SIGNAL can raise an explicit application condition. A routine should distinguish expected exceptions from failures that require rollback or propagation.

Related concepts: Data Constraints and Transactions and Stored Procedures.

Error handling allows you to catch and manage exceptions inside stored procedures or triggers using constructs like DECLARE HANDLER, SIGNAL, and RESIGNAL. These tools help you respond smartly instead of failing silently or abruptly.

• DECLARE CONTINUE HANDLER – catches errors and continues
• DECLARE EXIT HANDLER – catches errors and exits the block
• SIGNAL SQLSTATE – manually raise a custom error

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', 85),
(2, 'Diya Iyer', 92);

SQL · portable concepts; preserved examples identify their engine

DELIMITER $$

CREATE PROCEDURE insert_student (
  IN p_roll INT,
  IN p_name VARCHAR(50),
  IN p_marks INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Error: Roll number already exists or invalid data.' AS message;
  END;

  INSERT INTO students VALUES (p_roll, p_name, p_marks);
END$$

DELIMITER ;

Test it:

CALL insert_student(1, 'Sneha Patil', 88);

message
---------------------------------------
Error: Roll number already exists or invalid data.

SQL · portable concepts; preserved examples identify their engine

DELIMITER $$

CREATE PROCEDURE insert_and_continue ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SELECT 'Attempted insert failed, moving on...' AS status;
  END;

  INSERT INTO students VALUES (1, 'Mehul', 75); -- Will fail
  SELECT * FROM students;
END$$

DELIMITER ;

Call it:

CALL insert_and_continue();

status
-------------------------------
Attempted insert failed, moving on...

roll_no | name           | marks
--------+----------------+-------
1       | Aarav Sharma   | 85
2       | Diya Iyer      | 92

SQL · portable concepts; preserved examples identify their engine

DELIMITER $$

CREATE PROCEDURE validate_marks (
  IN p_marks INT
)
BEGIN
  IF p_marks > 100 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Marks cannot exceed 100';
  END IF;

  SELECT CONCAT('Accepted: ', p_marks) AS status;
END$$

DELIMITER ;

Try invalid input:

CALL validate_marks(105);

ERROR 1644 (45000): Marks cannot exceed 100

• Use EXIT HANDLER to stop on fatal errors.
• Use CONTINUE HANDLER to skip errors but continue processing.
• Use SIGNAL to enforce custom validation rules clearly.
• Always log or alert on exceptions — don’t fail silently.

• Prevent marks from exceeding limits (custom SIGNAL)
• Log failed insertions or updates (HANDLER + log table)
• Auto-handle missing attendance records without breaking flows

An EXIT handler does not by itself roll back the entire transaction. A duplicate-key error is not equivalent to every integrity violation. Do not swallow an error and report success; decide explicitly which writes remain valid.

Predict what remains after an insertion conflicts with the primary key. The original three rows should still be present.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

INSERT INTO practice_students VALUES (1, 'Another student', '9B', 90, 'Delhi');
SELECT COUNT(*) AS student_rows FROM practice_students;

The duplicate-key insert is rejected. This SQLite fixture verifies the constraint failure and surviving data; MySQL handler definitions in the preserved examples require a MySQL server.

The marked INSERT is expected to fail. Observe that error before running the following SELECT; do not report that the insert succeeded.

Database error handling determines how a program responds when an operation fails. A handler must match the intended condition and leave the data in an understood state.

The duplicate-key insert is rejected. This SQLite fixture verifies the constraint failure and surviving data; MySQL handler definitions in the preserved examples require a MySQL server.

Continue your understanding with Data Constraints and Transactions and Stored Procedures.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.