Error Handling
Catching and Responding to SQL Failures

Introduction

Databases are like schools — full of rules. And when rules are broken, something must respond. In SQL, errors happen when data is missing, wrong, or violates constraints. To gracefully handle these issues without crashing your entire system, SQL offers structured error handling mechanisms.

What is Error Handling in SQL?

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.

Syntax Overview

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

Sample Table – students

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);

1. Handling Duplicate Entry Error (EXIT HANDLER)

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.

2. Handling Error and Continuing Execution (CONTINUE HANDLER)

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

3. Raising a Custom Error with SIGNAL

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

Best Practices

• 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.

Real-World School Use Cases

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

Summary

Error handling ensures your SQL programs are not just powerful — but also resilient. Whether you’re inserting students, validating marks, or handling duplicates, proper use of HANDLER and SIGNAL helps your database respond thoughtfully, just like a good teacher correcting a mistake.

What’s Next?

Next up: SQL Optimization Techniques — learn how to write faster, leaner, and smarter queries that scale.