{"id":12694,"date":"2023-12-05T19:43:28","date_gmt":"2023-12-06T02:43:28","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12694"},"modified":"2023-12-05T19:43:39","modified_gmt":"2023-12-06T02:43:39","slug":"mysql-resignal","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-resignal\/","title":{"rendered":"MySQL RESIGNAL Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL RESIGNAL<\/code> statement to re-raise exceptions within stored programs.<\/p>\n\n\n\n

Introduction to MySQL RESIGNAL statement<\/h2>\n\n\n\n

The MySQL RESIGNAL<\/code> statement allows you to handle and re-raise an error condition within stored programs, including stored procedures, stored functions<\/a>, triggers<\/a>, and events<\/a>.<\/p>\n\n\n\n

To re-raise the error condition alone, you use the RESIGNAL<\/code> statement:<\/p>\n\n\n

RESIGNAL;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To re-raise an exception with new signal information, you use the RESIGNAL<\/code> statement with a SET<\/code> clause:<\/p>\n\n\n
RESIGNAL \nSET<\/span> signal_information_item \n[, signal_information_item] ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To re-raise a new condition value with new signal information:<\/p>\n\n\n
RESIGNAL condition_value\n[SET<\/span> signal_information_item \n[, signal_information_item] ...];<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL RESIGNAL statement examples<\/h2>\n\n\n\n
Let’s explore some examples to learn how to use the RESIGNAL<\/code> statement effectively.<\/p>\n\n\n\n
1) Using MySQL RESIGNAL to re-raise the same exception<\/h3>\n\n\n\n
The following example defines a stored procedure that drops a non-existing table XYZ<\/code>:<\/p>\n\n\n
DELIMITER \/\/\n\nCREATE<\/span> PROCEDURE<\/span> DropTableXYZ()\nBEGIN<\/span>\n  -- reraise the error<\/span>\n  DECLARE<\/span> EXIT<\/span> HANDLER<\/span> FOR<\/span> SQLEXCEPTION\n  BEGIN<\/span>\n    RESIGNAL;\n  END<\/span>;\n  \n  -- drop a table that doesn't exist<\/span>\n  DROP<\/span> TABLE<\/span> XYZ;\nEND<\/span>\/\/\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Inside the handler section, we use the RESIGNAL<\/code> statement to re-raise the exception. If any SQLEXCEPTION<\/code> occurs, the RESGINAL<\/code> statement re-raises the same exception.<\/p>\n\n\n\n
For example:<\/p>\n\n\n
CALL<\/span> DropTableXYZ();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
ERROR 1051 (42S02): Unknown table 'classicmodels.xyz'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using RESIGNAL statement with new signal information<\/h3>\n\n\n\n
The following example uses the RESIGNAL<\/code> statement to re-raise the exception but with new information by setting the MYSQL_ERRNO<\/code> to 5:<\/p>\n\n\n
DROP<\/span> PROCEDURE<\/span> DropTableXYZ;\n\nDELIMITER \/\/\n\nCREATE<\/span> PROCEDURE<\/span> DropTableXYZ()\nBEGIN<\/span>\n  -- reraise the error<\/span>\n  DECLARE<\/span> EXIT<\/span> HANDLER<\/span> FOR<\/span> SQLEXCEPTION\n  BEGIN<\/span>\n    RESIGNAL SET<\/span> MYSQL_ERRNO = 5<\/span>;\n  END<\/span>;\n  \n  -- drop a table that doesn't exist<\/span>\n  DROP<\/span> TABLE<\/span> XYZ;\nEND<\/span>\/\/\n\nDELIMITER ;\n\nCALL<\/span> DropTableXYZ();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you call the DropTableXYZ()<\/code> stored procedure, you’ll see a different error number:<\/p>\n\n\n
CALL<\/span> DropTableXYZ();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
ERROR 5 (42S02): Unknown table 'classicmodels.xyz'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the original error is 1051 not 5.<\/p>\n\n\n\n
3) Re-rasing exception with new condition value and signal information:<\/h3>\n\n\n\n
The following example modifies the stored procedure to re-raise an exception with a new condition and signal information:<\/p>\n\n\n
DELIMITER \/\/\n\nCREATE<\/span> PROCEDURE<\/span> DropTableXYZ()\nBEGIN<\/span>\n  -- reraise the error<\/span>\n  DECLARE<\/span> EXIT<\/span> HANDLER<\/span> FOR<\/span> SQLEXCEPTION\n  BEGIN<\/span>\n    RESIGNAL SQLSTATE<\/span> '45000'<\/span> SET<\/span> MYSQL_ERRNO = 5<\/span>;\n  END<\/span>;\n  \n  -- drop a table that doesn't exist<\/span>\n  DROP<\/span> TABLE<\/span> XYZ;\nEND<\/span>\/\/\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling the stored procedure will issue a different error:<\/p>\n\n\n
CALL<\/span> DropTableXYZ();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
ERROR 5 (45000): Unknown table 'classicmodels.xyz'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the RESIGNAL<\/code> statement to re-raise an exception.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t