MySQL - Signal

MySQL: Custom Error Handling with the SIGNAL Statement

In robust application development, simply letting a stored procedure or trigger fail with a generic database error is not ideal. The SIGNAL statement gives you precise control over error handling, allowing you to stop execution and return a custom, meaningful error message to the calling application.

SIGNAL is a critical component of advanced exception handling in MySQL, often used within stored procedures, stored functions, and triggers to enforce complex business logic.

Understanding SIGNAL

The SIGNAL statement is used to explicitly raise an error condition. When triggered, it passes an error value back to the caller. This can be a generic SQLSTATE or a named condition you’ve declared.

Syntax

SIGNAL sqlstate_value | condition_name
[SET signal_information_item_name = value, ...];

Key Components:

• sqlstate_value | condition_name: This specifies the error. An SQLSTATE is a 5-character string. By convention, SQLSTATE values starting with ‘45’ are for user-defined errors. A condition_name is a more readable alias for an SQLSTATE defined with DECLARE ... CONDITION.
• SET ...: This optional clause lets you customize the error. The most useful item is MESSAGE_TEXT, which provides a human-readable error message. You can also set MYSQL_ERRNO (a MySQL-specific error number).

Practical Examples

Let’s explore how to use SIGNAL to enforce business rules.

Example 1: Validating Input Parameters

Imagine a stored procedure that adds an employee but requires the age to be 18 or older. We can use SIGNAL to reject invalid entries.

DELIMITER //

CREATE PROCEDURE AddEmployee(
    IN p_name VARCHAR(100),
    IN p_age INT
)
BEGIN
    -- Business Rule: Employee must be at least 18 years old.
    IF p_age < 18 THEN
        -- Raise a custom error.
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Employee age must be 18 or greater.';
    END IF;

    -- If the age is valid, proceed with the insert.
    INSERT INTO Employees (name, age) VALUES (p_name, p_age);
    SELECT 'Employee added successfully.' AS message;
END //

DELIMITER ;

Now, let’s call this procedure.

-- This call will succeed.
CALL AddEmployee('John Doe', 30);

If we try to add an underage employee, our custom error is triggered.

-- This call will fail.
CALL AddEmployee('Jane Smith', 16);

Output of Failed Call

ERROR 1644 (45000): Employee age must be 18 or greater.

Example 2: Using a DECLARE ... CONDITION for Readability

For complex procedures with multiple custom checks, declaring named conditions makes the code cleaner and easier to maintain.

DELIMITER //

CREATE PROCEDURE UpdateProductPrice(
    IN p_product_id INT,
    IN p_new_price DECIMAL(10, 2)
)
BEGIN
    -- Declare a named condition for invalid price.
    DECLARE invalid_price CONDITION FOR SQLSTATE '45000';

    -- Business Rule: Price cannot be negative.
    IF p_new_price < 0 THEN
        -- Signal the named condition.
        SIGNAL invalid_price
        SET MESSAGE_TEXT = 'Product price cannot be negative.';
    END IF;

    -- Business Rule: Price cannot be more than double the current price (example).
    -- (A SELECT statement to get the current price would go here)

    UPDATE Products SET price = p_new_price WHERE id = p_product_id;
END //

DELIMITER ;

Calling this with a negative price will raise the error.

CALL UpdateProductPrice(101, -5.00);

Output

ERROR 1644 (45000): Product price cannot be negative.

Combining SIGNAL with DECLARE HANDLER

While SIGNAL raises an error, DECLARE HANDLER catches it. This allows you to perform cleanup actions (like rolling back a transaction) or log errors within a procedure before it exits.

DELIMITER //

CREATE PROCEDURE TransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';

    -- If an error occurs, roll back the transaction.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- Re-throw the error so the caller knows it failed.
    END;

    START TRANSACTION;

    -- Check if the source account has enough funds.
    IF (SELECT balance FROM Accounts WHERE id = from_account) < amount THEN
        SIGNAL insufficient_funds SET MESSAGE_TEXT = 'Insufficient funds.';
    END IF;

    -- Perform the transfer
    UPDATE Accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE Accounts SET balance = balance + amount WHERE id = to_account;

    COMMIT;
END //

DELIMITER ;

Best Practices

• Use SQLSTATE '45000': This is the designated class for ‘unhandled user-defined exception’. It clearly separates your custom business logic errors from standard MySQL server errors.
• Write Clear Messages: The MESSAGE_TEXT is for the developer or end-user. Make it descriptive and helpful.
• Keep it Simple: Don’t overuse SIGNAL. Standard constraints (NOT NULL, CHECK, FOREIGN KEY) are more efficient for simple data validation.
• Combine with Transactions: For multi-step operations, use SIGNAL within a transaction and set up a handler to ROLLBACK on failure, ensuring data consistency.