Error Handling & Handlers in Stored Programs0%

Creating User-Defined Functions (CREATE FUNCTION)

Error Handling & Handlers in Stored Programs

Advanced14 min read•Updated: 2026-09-12

Error Handling & Handlers in Stored Programs

In production applications, unhandled database errors (such as duplicate key violations, foreign key constraints, or connection drops) can cause procedures to crash mid-execution, leaving transactions dangling.

MySQL provides robust error handling through DECLARE ... HANDLER statements and the RESIGNAL command.

Declaring Handlers Syntax

1DECLARE { CONTINUE | EXIT } HANDLER 
2FOR condition_value 
3handler_statement;

Handler Action Types:

CONTINUE: Executes the handler statement and continues procedure execution at the next statement.
EXIT: Executes the handler statement and immediately terminates execution of the enclosing BEGIN ... END block.

Common Condition Values:

SQLEXCEPTION: Catches any general SQL error (codes not starting with 00, 01, or 02).
SQLWARNING: Catches warnings.
NOT FOUND: Triggers when a cursor reaches the end of data or a SELECT INTO returns zero rows.
Specific MySQL Error Code: e.g., 1062 (Duplicate Key Entry).

Practical Example: Transactional Safe Insert with Rollback Handler

Consider a procedure that inserts a user and customer profile atomically. If duplicate email error 1062 occurs, it automatically rolls back and outputs an error message:

1DELIMITER //
2 
3CREATE PROCEDURE RegisterUserAccount(
4    IN p_username VARCHAR(50),
5    IN p_email VARCHAR(100),
6    OUT p_status_code VARCHAR(20),
7    OUT p_error_message VARCHAR(255)
8)
9BEGIN
10    -- Flag to track errors
11    DECLARE error_occurred BOOLEAN DEFAULT FALSE;
12 
13    -- Declare EXIT handler for ANY general SQL exception
14    DECLARE EXIT HANDLER FOR SQLEXCEPTION
15    BEGIN
16        -- Rollback all uncommitted changes
17        ROLLBACK;
18        SET p_status_code = 'FAILED';
19        SET p_error_message = 'A database error occurred during registration. Transaction rolled back.';
20    END;
21 
22    -- Declare specific handler for Duplicate Key (1062)
23    DECLARE EXIT HANDLER FOR 1062
24    BEGIN
25        ROLLBACK;
26        SET p_status_code = 'DUPLICATE_EMAIL';
27        SET p_error_message = 'The specified email address is already registered.';
28    END;
29 
30    -- Begin Transaction
31    START TRANSACTION;
32 
33    INSERT INTO users (username, email) VALUES (p_username, p_email);
34    INSERT INTO user_profiles (user_id, bio) VALUES (LAST_INSERT_ID(), 'Welcome!');
35 
36    -- If no exception occurred, commit!
37    COMMIT;
38    SET p_status_code = 'SUCCESS';
39    SET p_error_message = 'Account registered successfully.';
40END //
41 
42DELIMITER ;

Throwing Custom Exceptions with SIGNAL

You can raise custom business errors using SIGNAL SQLSTATE:

1DELIMITER //
2 
3CREATE PROCEDURE WithdrawMoney(
4    IN p_acc_id INT,
5    IN p_amount DECIMAL(10, 2)
6)
7BEGIN
8    DECLARE current_bal DECIMAL(10, 2);
9 
10    SELECT balance INTO current_bal 
11    FROM bank_accounts 
12    WHERE account_id = p_acc_id;
13 
14    IF current_bal < p_amount THEN
15        -- Raise custom application error (SQLSTATE '45000' is generic user error)
16        SIGNAL SQLSTATE '45000'
17            SET MESSAGE_TEXT = 'Insufficient Funds: Withdrawal amount exceeds current balance!';
18    END IF;
19 
20    UPDATE bank_accounts 
21    SET balance = balance - p_amount 
22    WHERE account_id = p_acc_id;
23END //
24 
25DELIMITER ;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the difference between a CONTINUE handler and an EXIT handler in MySQL?

Question 2

Which condition category captures all general database SQL runtime errors?

Question 3

What is the purpose of the SIGNAL statement?

Question 4

Which standard SQLSTATE code is reserved for user-defined generic application exceptions?

Question 5

What error condition is triggered when a cursor advances past the final row or a SELECT INTO finds zero matches?

Next Lesson

Creating User-Defined Functions (CREATE FUNCTION)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Control Flow: Variables, Branches & Loops	Creating User-Defined Functions (CREATE FUNCTION)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Creating User-Defined Functions (CREATE FUNCTION)

Error Handling & Handlers in Stored Programs

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Error Handling & Handlers in Stored Programs

MySQL provides robust error handling through DECLARE ... HANDLER statements and the RESIGNAL command.

Declaring Handlers Syntax

1DECLARE { CONTINUE | EXIT } HANDLER 
2FOR condition_value 
3handler_statement;

Handler Action Types:

CONTINUE: Executes the handler statement and continues procedure execution at the next statement.
EXIT: Executes the handler statement and immediately terminates execution of the enclosing BEGIN ... END block.

Common Condition Values:

SQLEXCEPTION: Catches any general SQL error (codes not starting with 00, 01, or 02).
SQLWARNING: Catches warnings.
NOT FOUND: Triggers when a cursor reaches the end of data or a SELECT INTO returns zero rows.
Specific MySQL Error Code: e.g., 1062 (Duplicate Key Entry).

Practical Example: Transactional Safe Insert with Rollback Handler

Consider a procedure that inserts a user and customer profile atomically. If duplicate email error 1062 occurs, it automatically rolls back and outputs an error message:

1DELIMITER //
2 
3CREATE PROCEDURE RegisterUserAccount(
4    IN p_username VARCHAR(50),
5    IN p_email VARCHAR(100),
6    OUT p_status_code VARCHAR(20),
7    OUT p_error_message VARCHAR(255)
8)
9BEGIN
10    -- Flag to track errors
11    DECLARE error_occurred BOOLEAN DEFAULT FALSE;
12 
13    -- Declare EXIT handler for ANY general SQL exception
14    DECLARE EXIT HANDLER FOR SQLEXCEPTION
15    BEGIN
16        -- Rollback all uncommitted changes
17        ROLLBACK;
18        SET p_status_code = 'FAILED';
19        SET p_error_message = 'A database error occurred during registration. Transaction rolled back.';
20    END;
21 
22    -- Declare specific handler for Duplicate Key (1062)
23    DECLARE EXIT HANDLER FOR 1062
24    BEGIN
25        ROLLBACK;
26        SET p_status_code = 'DUPLICATE_EMAIL';
27        SET p_error_message = 'The specified email address is already registered.';
28    END;
29 
30    -- Begin Transaction
31    START TRANSACTION;
32 
33    INSERT INTO users (username, email) VALUES (p_username, p_email);
34    INSERT INTO user_profiles (user_id, bio) VALUES (LAST_INSERT_ID(), 'Welcome!');
35 
36    -- If no exception occurred, commit!
37    COMMIT;
38    SET p_status_code = 'SUCCESS';
39    SET p_error_message = 'Account registered successfully.';
40END //
41 
42DELIMITER ;

Throwing Custom Exceptions with SIGNAL

You can raise custom business errors using SIGNAL SQLSTATE:

1DELIMITER //
2 
3CREATE PROCEDURE WithdrawMoney(
4    IN p_acc_id INT,
5    IN p_amount DECIMAL(10, 2)
6)
7BEGIN
8    DECLARE current_bal DECIMAL(10, 2);
9 
10    SELECT balance INTO current_bal 
11    FROM bank_accounts 
12    WHERE account_id = p_acc_id;
13 
14    IF current_bal < p_amount THEN
15        -- Raise custom application error (SQLSTATE '45000' is generic user error)
16        SIGNAL SQLSTATE '45000'
17            SET MESSAGE_TEXT = 'Insufficient Funds: Withdrawal amount exceeds current balance!';
18    END IF;
19 
20    UPDATE bank_accounts 
21    SET balance = balance - p_amount 
22    WHERE account_id = p_acc_id;
23END //
24 
25DELIMITER ;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the difference between a CONTINUE handler and an EXIT handler in MySQL?

Question 2

Which condition category captures all general database SQL runtime errors?

Question 3

What is the purpose of the SIGNAL statement?

Question 4

Which standard SQLSTATE code is reserved for user-defined generic application exceptions?

Question 5

What error condition is triggered when a cursor advances past the final row or a SELECT INTO finds zero matches?

Next Lesson

Creating User-Defined Functions (CREATE FUNCTION)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Control Flow: Variables, Branches & Loops	Creating User-Defined Functions (CREATE FUNCTION)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next