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

Need a 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

Need a 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 ;

Need a 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 ;

Need a hint?

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