SQL RESIGNAL

What is SQL RESIGNAL?

RESIGNAL rethrows the current error or raises a new one inside an SQL exception handler.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RESIGNAL: MySQL 5.5+, MariaDB 10.3+, IBM DB2, PostgreSQL (via PL/pgSQL RAISE; no native RESIGNAL), Oracle (RAISE), SQL Standard 2003 condition-handling clause

SQL RESIGNAL Full Explanation

RESIGNAL is used inside a DECLARE ... HANDLER block (or equivalent condition-handling construct) to propagate the caught error upward or to raise a new error with a different SQLSTATE, message, or error number. When used without arguments, RESIGNAL passes the original error unchanged to the next outer handler or to the client. When supplied with a condition value and/or a SET clause, it allows the developer to modify the SQLSTATE code, message text, MySQL error number, or other diagnostic items before the exception continues to propagate. This is extremely useful for layering stored procedures, where inner routines want to surface cleaner or more domain-specific error messages while preserving the original failure context. RESIGNAL must be executed inside a handler; calling it outside a handler results in an error. Unlike SIGNAL, RESIGNAL never terminates the code immediately where it appears; instead, control returns to the runtime error-handling chain.

SQL RESIGNAL Syntax

RESIGNAL;

-- or with modifications
RESIGNAL [condition_value]
    [SET signal_information_item_list];

SQL RESIGNAL Parameters

condition_value (string or int) - Optional. A SQLSTATE value (e.g., '45000'), a named condition, or a MySQL error number that replaces the original.
signal_information_item_list (list) - Optional key-value pairs such as
- SQLSTATE - = 'state'

Example Queries Using SQL RESIGNAL

-- 1. Pass the original error upward
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    RESIGNAL; -- keeps original SQLSTATE and message
END;

-- 2. Wrap low-level error with custom message
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    RESIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Payment could not be processed',
            MYSQL_ERRNO   = 9001;
END;

-- 3. Inside a stored procedure
CREATE PROCEDURE transfer_funds(p_from INT, p_to INT, p_amt DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Log, then bubble up
        INSERT INTO error_log(msg) VALUES('transfer_funds failed');
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amt WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;
    COMMIT;
END;

Expected Output Using SQL RESIGNAL

RESIGNAL with no arguments re-emits the same error to the caller
When condition/value pairs are supplied, the caller receives the new SQLSTATE, message, and error number specified in the SET clause

Use Cases with SQL RESIGNAL

Preserve original error context while allowing higher-level handlers to react
Translate low-level database errors into business-domain messages
Add logging side effects inside handlers before letting the error propagate
Maintain layered stored procedure architectures without swallowing errors

Common Mistakes with SQL RESIGNAL

Using RESIGNAL outside a handler block (raises error 1644 in MySQL)
Forgetting to specify SQLSTATE '45000' for user-defined exceptions, causing conflicts with reserved codes
Assuming RESIGNAL stops execution immediately like SIGNAL; it first exits the handler and then propagates

First Introduced In

MySQL 5.5 (after SQL:2003 standard)

Frequently Asked Questions

What is the difference between RESIGNAL and SIGNAL?

SIGNAL raises a new error from regular code. RESIGNAL rethrows the current error from inside an active handler, optionally altering its details.

Can RESIGNAL change the SQLSTATE code?

Yes. Provide SQLSTATE 'value' in the RESIGNAL statement or in the SET clause to override the original SQLSTATE before the error propagates.

Does RESIGNAL end the procedure immediately?

Execution leaves the handler, then the error propagates to the next outer handler or client. It does not continue normal code after the handler.

Is RESIGNAL supported in MariaDB and DB2?

MariaDB 10.3 and newer and IBM DB2 support RESIGNAL with similar syntax, making it portable across these systems.