MySQL Raise Error with SIGNAL

In this tutorial, we will learn how to raise an error using the signal statement. The SIGNAL and RESIGNAL statements are very similar to each other except few points. We will see the details and examples of the SIGNAL statement in this article and for the RESIGNAL statement, we will have a separate tutorial.

Also read: MySQL Index Hints- FORCE INDEX

Introduction to MySQL SIGNAL

The SIGNAL statement is used to throw an error in the stored programs such as stored procedures, stored programs, triggers or events. Using the SIGNAL, you can return an error number, SQL STATE value or a custom message.

To execute the SIGNAL statement, you don’t need any special privilege.

In short, you can use the SIGNAL statement to return a custom message when an error occurs in the program.

Also read: Variables in MySQL Stored Procedures – Easy Implementation

MySQL SIGNAL Statement Syntax

The following syntax shows the correct way to use the SIGNAL statement-

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
}

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

Here,

The condition_value tells the error value to be thrown. The condtion_value can be an SQLSTATE or a condition_name which refers to the previously created named definition with the DECLARE … CONDITION statement.

The SQLSTATE value for a SIGNAL statement shouldn’t begin with the number “00” because these values imply success rather than an error. Instead, they should begin with a value other than ’00’. Either the SQLSTATE value is explicitly provided in the SIGNAL statement or it is referenced by a named condition in the statement, but either way, it is true. A Bad SQLSTATE problem manifests itself when the value is incorrect.

Optionally, the SIGNAL statement includes a SET clause with a list of condition information item name = simple_value_specification assignments, separated by commas, for each signal item.

Use the code “45000,” which stands for “unhandled user-defined exception,” to indicate a generic SQLSTATE value.

MySQL SIGNAL Statement Examples

Now we will take some examples to demonstrate the use of the SIGNAL statement.

Here, we will create a procedure to show the use of warnings and errors with the signal statement.

DROP PROCEDURE IF EXISTS signalDemo;
DELIMITER //
CREATE PROCEDURE signalDemo(num INT)
BEGIN
IF num = 0 THEN
  SIGNAL SQLSTATE '01000';
ELSEIF num = 1 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT="error raised 1";
ELSEIF num = 2 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT="error raised 2";
ELSE 
  SIGNAL SQLSTATE '01000'
  SET MESSAGE_TEXT="warning raised" , MYSQL_ERRNO = 1000;
END IF;
END;
//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

Here, if the value of num is 0, the procedure will signal a warning because the SQLSTATE value that starts with ’01’ is the warning.

Note that, the warning doesn’t terminate the procedure.

When the value of num is 1 or 2, the procedure signals an error and sets the message_text item. Here, after the error is caught, the procedure terminates and the text that you set earlier will be displayed.

If the value of num is anything else, the procedure signals the warning and sets the custom message with the error number.

Let’s examine everything by calling the procedures.

CALL signalDemo(0);Code language: SQL (Structured Query Language) (sql)

CALL signalDemo(1);
CALL signalDemo(2);Code language: SQL (Structured Query Language) (sql)

CALL signalDemo(3);Code language: SQL (Structured Query Language) (sql)

A specific SQLSTATE value is used to signal the condition if the SIGNAL statement specifies that value.

Let’s take an example of divide by zero error.

DROP PROCEDURE IF EXISTS div_by_zero;
DELIMITER //
CREATE PROCEDURE div_by_zero(divisor INT)
BEGIN
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012';
END IF;
END;
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

You can also give them a name to the signal statement. If you do so, the condition must be defined using the SQLSTATE value instead of the MySQL error number.

Let’s re-write the above procedure using the named signal statement.

DROP PROCEDURE IF EXISTS div_by_zero;
DELIMITER //
CREATE PROCEDURE div_by_zero(divisor INT)
BEGIN
DECLARE div_by_zero_error CONDITION FOR SQLSTATE '22012';
IF divisor = 0 THEN
SIGNAL div_by_zero_error;
END IF;
END;
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Summary

In this tutorial, we have learned about the SIGNAL statement in MySQL. It is a broad topic to learn and understand. However, it is one of the most important topics in MySQL. It is absolutely worth learning the SIGNAL topic. If you want to get a more detailed view of the SIGNAL, you can check out its official documentation.

References

MySQL official documentation on the SIGNAL.