SQL SIGNAL

What is the SQL SIGNAL statement?

Raises a user-defined error condition inside stored programs so execution stops and control returns to the caller.

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 SIGNAL:

SQL SIGNAL Full Explanation

SQL SIGNAL is an error-handling statement defined in the SQL/PSM standard and implemented in MySQL and MariaDB. When executed inside a stored procedure, function, trigger, or event, SIGNAL immediately aborts the current statement block and returns a custom SQLSTATE along with optional diagnostic information to the client or outer handler. Because SIGNAL behaves like a server-generated error, it automatically rolls back the current statement (or the entire transaction if autocommit is on) and can be caught by DECLARE ... HANDLER blocks higher in the call stack.Developers use SIGNAL to enforce business rules, validate input, or re-throw lower-level errors with clearer messages. The statement supports both numeric SQLSTATE codes and named conditions previously declared with DECLARE CONDITION. Optional SET clauses allow population of the diagnostics area (MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, etc.).Key caveats:- SIGNAL is not available outside stored programs (you cannot run it as a standalone query in MySQL clients).- Only five-character SQLSTATE strings in the ranges '01', '02', or 'HY' (or vendor-specific '45') are allowed.- If no MESSAGE_TEXT is provided, MySQL returns a generic "Unhandled user-defined exception" message.

SQL SIGNAL Syntax

SIGNAL SQLSTATE <sqlstate_value>
    [SET <condition_item_name> = <expr>[, ...]];

SIGNAL <condition_name>
    [SET <condition_item_name> = <expr>[, ...]];

SQL SIGNAL Parameters

- sqlstate_value: CHAR (5) - Five-character SQLSTATE return code such as '45000' (generic unhandled user exception).
- condition_name (Identifier) - A symbolic name declared earlier with DECLARE CONDITION.
- condition_item_name - One of MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_NAME, CONSTRAINT_SCHEMA, CONSTRAINT_CATALOG, COLUMN_NAME, CURSOR_NAME, SUBCLASS_ORIGIN.
- expr - Any SQL expression resolving to the data type required by the condition item.

Example Queries Using SQL SIGNAL

-- Reject negative order quantities
delimiter //
CREATE PROCEDURE add_order(p_qty INT)
BEGIN
    IF p_qty < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity cannot be negative',
                MYSQL_ERRNO = 1001;
    END IF;
    INSERT INTO orders(qty) VALUES (p_qty);
END //

delimiter ;

-- Using a named condition
DECLARE insufficient_funds CONDITION FOR SQLSTATE '45002';
IF acct_balance < amount THEN
    SIGNAL insufficient_funds
        SET MESSAGE_TEXT = 'Insufficient account balance';
END IF;

Expected Output Using SQL SIGNAL

The procedure or trigger halts
The client receives error 1001 (HY000) with SQLSTATE 45000 and message "Quantity cannot be negative"
Any uncommitted statement work is rolled back

Use Cases with SQL SIGNAL

Enforcing complex validation rules inside stored procedures
Returning readable error messages from triggers instead of generic constraint failures
Re-throwing caught exceptions with additional context using RESIGNAL
Aborting execution when business-logic preconditions are not met

Common Mistakes with SQL SIGNAL

Using SIGNAL outside a stored program block (results in a syntax error)
Supplying an invalid or non-five-character SQLSTATE
Forgetting to set MESSAGE_TEXT, leading to unclear generic error messages
Confusing SIGNAL with RESIGNAL; RESIGNAL is used inside a handler to pass along the caught error

First Introduced In

MySQL 5.5 (2010)

Frequently Asked Questions

What is the difference between SIGNAL and RESIGNAL?

SIGNAL creates a new error condition, while RESIGNAL rethrows the error that a handler has caught, optionally modifying its diagnostics.

Can I use SQL SIGNAL in a simple SELECT query?

No. SIGNAL is legal only inside stored procedures, functions, triggers, or event scheduler bodies.

Which SQLSTATE code should I choose?

Use '45000' for generic user errors. Choose codes in class '42' for syntax issues, '22' for data exceptions, or vendor class '45' for application-defined errors.

Does SIGNAL automatically roll back transactions?

SIGNAL rolls back the statement that triggered it. In autocommit mode this ends the transaction; otherwise you can still commit or roll back the surrounding transaction explicitly.