RESIGNAL Alone

A simple RESIGNAL alone means "pass on the error with no change." It restores the last diagnostics area and makes it the current diagnostics area. That is, it "pops" the diagnostics area stack.

Within a condition handler that catches a condition, one use for RESIGNAL alone is to perform some other actions, and then pass on without change the original condition information (the information that existed before entry into the handler).

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; END IF;
 END;
 DROP TABLE xx;
END//
delimiter ;
SET @error_count = 0;
SET @a = 0;
CALL p();

The DROP TABLE xx statement fails. The diagnostics area stack looks like this:

1. ERROR 1051 (42S02): Unknown table 'xx'

Then execution enters the EXIT handler. It starts by pushing the top of the diagnostics area stack, which now looks like this:

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

Usually a procedure statement clears the first diagnostics area (also called the "current" diagnostics area). BEGIN is an exception, it does not clear, it does nothing. SET is not an exception, it clears, performs the operation, and then produces a result of "success." The diagnostics area stack now looks like this:

1. ERROR 0000 (00000): Successful operation
2. ERROR 1051 (42S02): Unknown table 'xx'

At this point, if @a = 0, RESIGNAL pops the diagnostics area stack, which now looks like this:

1. ERROR 1051 (42S02): Unknown table 'xx'

And that is what the caller sees.

If @a is not 0, the handler simply ends, which means that there is no more use for the last diagnostics area (it has been "handled"), so it can be thrown away. The diagnostics area stack looks like this:

1. ERROR 0000 (00000): Successful operation

The details make it look complex, but the end result is quite useful: Handlers can execute without destroying information about the condition that caused activation of the handler.

Retornar