Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Error Handling in Routines

Explore how to implement effective error handling in MySQL stored routines. Learn to declare handlers for conditions like NOT FOUND, use RESIGNAL to raise custom errors, and apply SELECT ... INTO to fetch data safely. This lesson guides you through writing validation procedures that improve reliability and error detection in SQL routines.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
DELIMITER $$
CREATE PROCEDURE ValidateCustomerExists(IN cust_id INT)
BEGIN
    DECLARE v_name VARCHAR(50);
    -- Handler to catch missing rows from SELECT ... INTO
    DECLARE EXIT HANDLER FOR NOT FOUND
    BEGIN
        RESIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Customer does not exist';
    END;
    -- Attempt to read the customer record
    SELECT CustomerName
    INTO   v_name
    FROM   Customers
    WHERE  CustomerID = cust_id;
    -- If found, return confirmation
    SELECT CONCAT('Customer exists: ', v_name) AS Message;
END$$
DELIMITER ;
-- This call should succeed because CustomerID = 1 exists
CALL ValidateCustomerExists(1);
-- This call should throw the custom error:
-- Uncomment to test
-- CALL ValidateCustomerExists(99999);

Explanation

The explanation of the solution code is given below:

  • Line 1: Set the statement delimiter to $$ so the routine body can use semicolons safely.

  • Line 3: Start the procedure definition for ValidateCustomerExists, which accepts the input parameter cust_id.

  • Line 4: Begin the procedure block with BEGIN, defining the scope for all subsequent declarations and statements.

  • Line 5: Declare a local variable v_name to hold the customer’s name.

  • Lines 8–12: Declare an EXIT handler for the NOT FOUND condition. If a SELECT ... INTO does not find a row, this handler is triggered, ...