MySQL SIGNAL Statement

Summary: in this tutorial, you will learn how to use the MySQL SIGNAL statement to raise error conditions within stored procedures.

Introduction to MySQL SIGNAL statement

The MySQL SIGNAL statement allows you to raise an exception within a stored program, including a stored procedure, a stored function, a trigger, or an event.

Here’s the syntax of the MySQL SIGNAL statement:

SIGNAL condition_value
[SET signal_information_item, 
     signal_information_item,...];Code language: SQL (Structured Query Language) (sql)

In this syntax:

condition_value

The condition value indicates the error value that you want to raise. It can be either:

• An SQLSTATE value. The SQLSTATE value should not start with '00' because it doesn’t indicate an error. To signal a generic SQLSTATE value, you use '45000', which indicates an "unhandled user-defined exception".
• A named condition is defined with DECLARE ... CONDITION statement.

signal_information_item

The SIGNAL statement may include a SET clause that contains multiple signal items. Each signal item is in the following format:

condition_information_item_name = simple_value_specificationCode language: SQL (Structured Query Language) (sql)

You use commas to separate multiple signal items.

MySQL SIGNAL statement example

Let’s set up a new database called hr with an employees table for demonstration:

-- Create a sample database and switch to it
CREATE DATABASE IF NOT EXISTS hr;
USE hr;

-- Create a sample employee table
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

-- Insert some sample data
INSERT INTO employees (id, name, salary) 
VALUES
    (1, 'John Doe', 50000),
    (2, 'Jane Smith', 75000),
    (3, 'Bob Johnson', 90000);Code language: SQL (Structured Query Language) (sql)

The following example creates a stored procedure that updates the salary of an employee specified by an employee number and raises an error if the employee is not found or the salary is negative:

DELIMITER //

CREATE PROCEDURE update_salary(
	IN p_employee_id INT,
    IN p_salary DECIMAL
)
BEGIN 
	DECLARE employee_count INT;
    
    -- check if employee exists
    SELECT COUNT(*) INTO employee_count 
    FROM employees
    WHERE id = p_employee_id;
    
    IF employee_count = 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Employee not found';
    END IF;
    
    -- validate salary
    IF p_salary < 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
    
    -- if every is fine, update the salary
    UPDATE employees
    SET salary = p_salary
    WHERE id = p_employee_id;    

END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

How it works.

First, declare the employee_count variable and count the number of employees based on the p_employee_id parameter:

    DECLARE employee_count INT;
    
    -- check if employee exists
    SELECT COUNT(*) INTO employee_count 
    FROM employees
    WHERE id = p_employee_id;Code language: SQL (Structured Query Language) (sql)

Second, raise an error if the employee_count is zero using the SIGNAL statement:

    IF employee_count = 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Employee not found';
    END IF;Code language: SQL (Structured Query Language) (sql)

Third, raise another error if the salary is negative:

    IF p_salary < 0 THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;Code language: SQL (Structured Query Language) (sql)

Finally, update the salary if the employee exists and the salary is positive:

    UPDATE employees
    SET salary = p_salary
    WHERE id = p_employee_id;   Code language: SQL (Structured Query Language) (sql)

The following statement calls the update_salary procedure and raises an employee not found error:

CALL update_salary(1, 7000);Code language: SQL (Structured Query Language) (sql)

Output:

ERROR 1644 (45000): Employee not foundCode language: SQL (Structured Query Language) (sql)

If you use a negative salary, you’ll get the following error:

CALL update_salary(1, -7000);Code language: SQL (Structured Query Language) (sql)

Error:

ERROR 1644 (45000): Salary cannot be negativeCode language: SQL (Structured Query Language) (sql)

Summary

• Use MySQL SIGNAL statement to raise error conditions within stored procedures.