RESIGNAL with a Condition Value and Optional New Signal Information


RESIGNAL with a condition value means "push a condition into the current diagnostics stack area." If the SET clause is present, it also changes the error information.

RESIGNAL condition_value
 [SET signal_information_item [, signal_information_item] ...];

This form of RESIGNAL restores the last diagnostics area and makes it the current diagnostics area. That is, it "pops" the diagnostics area stack, which is the same as what a simple RESIGNAL alone would do. However, it also changes the diagnostics area depending on the condition value or signal information.

Example:

DROP TABLE IF EXISTS xx;
delimiter //
CREATE PROCEDURE p ()
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
 SET @error_count = @error_count + 1;
 IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF;
 END;
 DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
SET @@max_error_count = 2;
CALL p();
SHOW ERRORS;

This is similar to the previous example, and the effects are the same, except that if RESIGNAL happens the current condition area looks different at the end. (The reason the condition is added rather than replaced is the use of a condition value.)

The RESIGNAL statement includes a condition value (SQLSTATE '45000'), so it "pushes" a new condition area, resulting in a diagnostics area stack that looks like this:

1. (condition 1) ERROR 5 (45000) Unknown table 'xx'
 (condition 2) ERROR 1051 (42S02): Unknown table 'xx'

The result of CALL p() and SHOW ERRORS for this example is:

Retornar