RESIGNAL

Syntax

SIGNAL [error_condition] [SET error_property [, error_property] ...]

error_condition: SQLSTATE [VALUE] 'sqlstate_value'

error_property: error_property_name = <error_property_value>

error_property_name: CLASS_ORIGIN

Description

RESIGNAL's syntax and semantics are very similar to SIGNAL. This statement can only be used within an error HANDLER. It produces an error, like SIGNAL. RESIGNAL clauses are the same as SIGNAL, except that they all are optional, even SQLSTATE. All the properties which are not specified in RESIGNAL, will be identical to the properties of the error that was received by the error HANDLER. For a description of the clauses, see SIGNAL.

RESIGNAL, without any clauses, produces an error which is exactly identical to the error that was received by HANDLER.

If used out of a HANDLER construct, RESIGNAL produces the following error:

ERROR 1645 (0K000): RESIGNAL when handler not active

Examples

The following procedure tries to query two tables which don't exist, producing a 1146 error in both cases. Those errors will trigger the HANDLER. The first time the error will be ignored and the client will not receive it, but the second time, the error is RESIGNALed, so the client will receive it.

CREATE PROCEDURE test_error()
BEGIN
	DECLARE CONTINUE HANDLER
		FOR 1146
	BEGIN
		IF @hide_errors IS FALSE THEN
			RESIGNAL;
		END IF;
	END;
	SET @hide_errors = TRUE;
	SELECT 'Next error will be ignored' AS msg;
	SELECT `c` FROM `temptab_one`;
	SELECT 'Next error won''t be ignored' AS msg;
	SET @hide_errors = FALSE;
	SELECT `c` FROM `temptab_two`;
END;

MariaDB [test]> CALL test_error();
+----------------------------+
| msg                        |
+----------------------------+
| Next error will be ignored |
+----------------------------+
1 row in set (0.01 sec)

+-----------------------------+
| msg                         |
+-----------------------------+
| Next error won't be ignored |
+-----------------------------+
1 row in set (0.85 sec)

ERROR 1146 (42S02): Table 'test.temptab_two' doesn't exist

The following procedure RESIGNALs an error, modifying only the error message to clarify the cause of the problem.

CREATE PROCEDURE test_error()
BEGIN
	DECLARE CONTINUE HANDLER
		FOR 1146
	BEGIN
		RESIGNAL SET
			MESSAGE_TEXT = '`temptab` does not exist';
	END;
	SELECT `c` FROM `temptab`;
END;

MariaDB [test]> CALL test_error();
ERROR 1146 (42S02): `temptab` does not exist