Error handling in procedures in MySQL - Mini Project: Build & Apply

Error Handling in MySQL Stored Procedures

📖 Scenario: You are managing a small online store database. You want to create a stored procedure that inserts new orders into the orders table. Sometimes, the insertion might fail due to invalid data or duplicate order IDs. To keep the database consistent, you want to handle these errors gracefully inside the procedure.

🎯 Goal: Create a MySQL stored procedure called InsertOrder that inserts a new order into the orders table. Add error handling using DECLARE CONTINUE HANDLER to catch duplicate entry errors and other SQL exceptions. The procedure should set an output parameter status_message to inform if the insertion was successful or if an error occurred.

📋 What You'll Learn

Create a table orders with columns order_id (INT, primary key), customer_name (VARCHAR(100)), and order_amount (DECIMAL(10,2))

Create a stored procedure InsertOrder with input parameters p_order_id, p_customer_name, p_order_amount and an output parameter status_message

Use DECLARE CONTINUE HANDLER to catch duplicate entry errors (error code 1062) and set status_message accordingly

Use a general DECLARE CONTINUE HANDLER for other SQL exceptions to set status_message to a generic error message

Insert the new order inside the procedure and set status_message to 'Order inserted successfully' if no error occurs

💡 Why This Matters

🌍 Real World

Error handling in stored procedures is essential for maintaining data integrity and providing clear feedback when database operations fail, such as inserting orders in an e-commerce system.

💼 Career

Database developers and administrators often write stored procedures with error handling to ensure robust applications and to simplify debugging and maintenance.

1

Create the orders table

Create a table called orders with columns: order_id as INT and primary key, customer_name as VARCHAR(100), and order_amount as DECIMAL(10,2).

MySQL

-- Create the orders table
-- Your code here

Hint

Use CREATE TABLE with the specified columns and data types. Remember to set order_id as the primary key.

2

Start the InsertOrder procedure with parameters

Create a stored procedure called InsertOrder with input parameters p_order_id INT, p_customer_name VARCHAR(100), p_order_amount DECIMAL(10,2), and an output parameter status_message VARCHAR(255).

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

-- Create the InsertOrder procedure with input and output parameters
-- Your code here

Hint

Use CREATE PROCEDURE with the specified parameters. Use DELIMITER $$ and DELIMITER ; to define the procedure.

3

Add error handlers inside the procedure

Inside the InsertOrder procedure, add two DECLARE CONTINUE HANDLER statements: one for SQLSTATE '23000' to catch duplicate entry errors and set status_message to 'Duplicate order ID', and another for SQLEXCEPTION to catch other errors and set status_message to 'Error inserting order'.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE InsertOrder(
  IN p_order_id INT,
  IN p_customer_name VARCHAR(100),
  IN p_order_amount DECIMAL(10,2),
  OUT status_message VARCHAR(255)
)
BEGIN
  -- Add error handlers here
  -- Your code here
END$$
DELIMITER ;

Hint

Use DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' to catch duplicate key errors. Use DECLARE CONTINUE HANDLER FOR SQLEXCEPTION for other errors. Set the status_message variable inside each handler.

4

Insert the order and set success message

Inside the InsertOrder procedure, after the error handlers, insert a new row into the orders table using the input parameters. Then set status_message to 'Order inserted successfully' if no error occurs.

MySQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  order_amount DECIMAL(10,2)
);

DELIMITER $$
CREATE PROCEDURE InsertOrder(
  IN p_order_id INT,
  IN p_customer_name VARCHAR(100),
  IN p_order_amount DECIMAL(10,2),
  OUT status_message VARCHAR(255)
)
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET status_message = 'Duplicate order ID';
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET status_message = 'Error inserting order';

  -- Insert the order and set success message
  -- Your code here
END$$
DELIMITER ;

Hint

Use INSERT INTO orders with the input parameters. Then set status_message to the success message after the insert statement.

Practice

1. What is the main purpose of using DECLARE HANDLER in a MySQL stored procedure?

easy

A. To declare variables inside the procedure

B. To catch and handle errors during procedure execution

C. To create temporary tables for data processing

D. To optimize query performance automatically

Solution

1. Step 1: Understand the role of DECLARE HANDLER

   DECLARE HANDLER is used to define actions when certain conditions or errors occur during procedure execution.

2. Step 2: Identify the purpose in error handling

   It helps catch errors and decide whether to continue or exit, preventing crashes and allowing custom responses.

3. Final Answer:

   To catch and handle errors during procedure execution -> Option B

4. Quick Check:

   Error handling = DECLARE HANDLER usage [OK]

Hint: DECLARE HANDLER is for managing errors inside procedures [OK]

Common Mistakes:

• Confusing DECLARE HANDLER with variable declaration
• Thinking it creates tables or indexes
• Assuming it optimizes queries automatically

2. Which of the following is the correct syntax to declare a handler for SQL exceptions that continues execution in MySQL?

easy

A. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END;

B. DECLARE HANDLER CONTINUE FOR SQLEXCEPTION;

C. DECLARE HANDLER FOR CONTINUE SQLEXCEPTION;

D. DECLARE HANDLER FOR SQLEXCEPTION CONTINUE;

Solution

1. Step 1: Recall correct DECLARE HANDLER syntax

   The syntax is: DECLARE [CONTINUE|EXIT] HANDLER FOR condition statement;

2. Step 2: Match syntax with options

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; correctly uses DECLARE CONTINUE HANDLER FOR SQLEXCEPTION followed by a statement block.

3. Final Answer:

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; -> Option A

4. Quick Check:

   Correct syntax = DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ... END; [OK]

Hint: Syntax: DECLARE CONTINUE HANDLER FOR error BEGIN...END [OK]

Common Mistakes:

• Placing CONTINUE after HANDLER incorrectly
• Omitting BEGIN...END block for multiple statements
• Wrong order of keywords

3. Consider this procedure snippet:

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error_flag = 1;
SET @error_flag = 0;
INSERT INTO users(id, name) VALUES(1, 'Alice');
INSERT INTO users(id, name) VALUES(1, 'Bob');

Assuming id is a primary key, what will be the value of @error_flag after execution?

medium

A. 1

B. 0

C. NULL

D. Procedure will crash without setting @error_flag

Solution

1. Step 1: Analyze the inserts and handler

   The first insert succeeds. The second insert violates primary key (duplicate id), causing an SQLEXCEPTION.

2. Step 2: Understand EXIT HANDLER effect

   The EXIT HANDLER sets @error_flag = 1 and stops further execution.

3. Final Answer:

   1 -> Option A

4. Quick Check:

   Duplicate key error triggers EXIT HANDLER = 1 [OK]

Hint: EXIT HANDLER sets flag on error and stops execution [OK]

Common Mistakes:

• Assuming @error_flag stays 0 despite error
• Thinking procedure continues after EXIT HANDLER
• Confusing CONTINUE and EXIT handlers

4. Identify the error in this MySQL procedure snippet:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE done BOOLEAN DEFAULT FALSE;
WHILE done = FALSE DO
  -- some code
END WHILE;

medium

A. NOT FOUND is not a valid condition for handler

B. Missing semicolon after DECLARE statements

C. WHILE loop condition syntax is incorrect

D. Handler declared before variable 'done' is declared

Solution

1. Step 1: Check declaration order rules

   In MySQL procedures, variables must be declared before handlers.

2. Step 2: Identify declaration order in snippet

   The handler is declared before the variable 'done', which is invalid.

3. Final Answer:

   Handler declared before variable 'done' is declared -> Option D

4. Quick Check:

   Variables before handlers = correct order [OK]

Hint: Declare variables before handlers in procedures [OK]

Common Mistakes:

• Declaring handlers before variables
• Confusing NOT FOUND condition usage
• Ignoring declaration order rules

5. You want to write a procedure that tries to insert a new user but if a duplicate key error occurs, it should update the user's name instead. Which error handling approach is best?

hard

A. Use DECLARE CONTINUE HANDLER for NOT FOUND to insert new user

B. Use DECLARE EXIT HANDLER for any error to rollback transaction

C. Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement

D. Use no handler and rely on client application to handle errors

Solution

1. Step 1: Understand the goal

   Insert user; if duplicate key error occurs, update existing user's name instead.

2. Step 2: Choose handler type

   CONTINUE HANDLER allows procedure to continue after catching duplicate key error and run UPDATE.

3. Step 3: Evaluate other options

   EXIT HANDLER stops execution, NOT FOUND is unrelated, and no handler defers error handling outside procedure.

4. Final Answer:

   Use DECLARE CONTINUE HANDLER for duplicate key error to run UPDATE statement -> Option C

5. Quick Check:

   CONTINUE HANDLER for duplicate key = update fallback [OK]

Hint: Use CONTINUE HANDLER to catch duplicate key and update [OK]

Common Mistakes:

• Using EXIT HANDLER which stops procedure
• Using NOT FOUND for duplicate key errors
• Ignoring error handling inside procedure