MySQL Resignal – A Complete Guide

In this tutorial, we will learn about the RESIGNAL statement. The RESIGNAL statement is pretty much similar to the SIGNAL statement by the functionality and the syntax except for some properties. So without wasting the time, let’s jump into it!

Introduction to MySQL RESIGNAL

While writing stored programs such as stored procedures or stored functions, you often need to handle the errors that might break your programs. In this case, you can use the SIGNAL or RESIGNAL statements to handle the error.

These statements allow us to write an error condition and raise an error. This helps us by not showing the system-generated error to the user but the meaningful error message.

We already have written a detailed tutorial on the SIGNAL statement that you can read here. As said earlier, the RESIGNAL statement is very similar to the SIGNAL statement, except-

• If the RESIGNAL statement is not used within an error or warning handler, an error message stating that “RESIGNAL while the handler is not active” will be shown. Keep in mind that the SIGNAL statement can be used anywhere inside a stored process.
• All of the RESIGNAL statement’s properties can be skipped.

RESIGNAL enables the handling of errors as well as the return of error information. Otherwise, the data that made the handler activated is destroyed by running a SQL query inside the handler.

MySQL RESIGNAL Syntax

The syntax of the RESIGNAL statement is the same as that of the SIGNAL statement.

RESIGNAL [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.

Note that, all attributes are the same as those passed to the condition handler if you only use the RESIGNAL statement.

MySQL RESIGNAL Example

Let’s write a stored procedure to demonstrate the use of the RESIGNAL statement.

Here, we will write a stored procedure for generating the divide by zero error.

DROP PROCEDURE IF EXISTS divByZero;
DELIMITER //
CREATE PROCEDURE divByZero(IN num INT,IN deno INT,OUT res FLOAT)
BEGIN
DECLARE div_by_zero CONDITION FOR SQLSTATE '22012';
DECLARE CONTINUE HANDLER FOR div_by_zero
RESIGNAL SET MESSAGE_TEXT = 'Division by zero error';
IF deno = 0 
THEN
SIGNAL div_by_zero;
ELSE
SET res=num/deno;
END IF;
END
//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here, we create a stored procedure which has three parameters- numerator, denominator and the result.

Then we create the declare-condition statement which can be referred to in the declare-handler statement on the next line.

Then we write the resignal statement and set the error message.

On the next line, we check if the denominator is zero. If it is zero then we call the corresponding named error condition. If it is not, we simply assign the result to the output variable.

Let’s now call the procedure by giving correct inputs and let’s see the result. Then we will provide the denominator 0 to check the result.

CALL divByZero(10,5,@res);
CALL divByZero(10,4,@res);
Code language: SQL (Structured Query Language) (sql)

As you can see, we got the correct output. Let’s call the procedure by giving the zero denominators.

CALL divByZero(10,0,@res);
CALL divByZero(5,0,@res);
Code language: SQL (Structured Query Language) (sql)

As you can see here, we got the error message the same as we specified in the above program.

Conclusion

In this tutorial, we have learned about the RESIGNAL statement. I hope you have understood the difference between the SIGNAL and RESIGNAL statements by their functionality. If you find this tutorial helpful, don’t forget to share it with your friends!