MySQL RESIGNAL Statement

Summary: in this tutorial, you will learn how to use the MySQL RESIGNAL statement to re-raise exceptions within stored programs.

Introduction to MySQL RESIGNAL statement

The MySQL RESIGNAL statement allows you to handle and re-raise an error condition within stored programs, including stored procedures, stored functions, triggers, and events.

To re-raise the error condition alone, you use the RESIGNAL statement:

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

To re-raise an exception with new signal information, you use the RESIGNAL statement with a SET clause:

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

To re-raise a new condition value with new signal information:

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

MySQL RESIGNAL statement examples

Let’s explore some examples to learn how to use the RESIGNAL statement effectively.

1) Using MySQL RESIGNAL to re-raise the same exception

The following example defines a stored procedure that drops a non-existing table XYZ:

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

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

Inside the handler section, we use the RESIGNAL statement to re-raise the exception. If any SQLEXCEPTION occurs, the RESGINAL statement re-raises the same exception.

For example:

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

Output:

ERROR 1051 (42S02): Unknown table 'classicmodels.xyz'Code language: SQL (Structured Query Language) (sql)

2) Using RESIGNAL statement with new signal information

The following example uses the RESIGNAL statement to re-raise the exception but with new information by setting the MYSQL_ERRNO to 5:

DROP PROCEDURE DropTableXYZ;

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL SET MYSQL_ERRNO = 5;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

DELIMITER ;

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

If you call the DropTableXYZ() stored procedure, you’ll see a different error number:

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

Output:

ERROR 5 (42S02): Unknown table 'classicmodels.xyz'Code language: SQL (Structured Query Language) (sql)

Notice that the original error is 1051 not 5.

3) Re-rasing exception with new condition value and signal information:

The following example modifies the stored procedure to re-raise an exception with a new condition and signal information:

DELIMITER //

CREATE PROCEDURE DropTableXYZ()
BEGIN
  -- reraise the error
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 5;
  END;
  
  -- drop a table that doesn't exist
  DROP TABLE XYZ;
END//

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

Calling the stored procedure will issue a different error:

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

Error:

ERROR 5 (45000): Unknown table 'classicmodels.xyz'Code language: SQL (Structured Query Language) (sql)

Summary

• Use the RESIGNAL statement to re-raise an exception.