resignal

SQL/PL

Resignal a condition or exception

Syntax

resignal_stmt:
          RESIGNAL specific_condition [SET MESSAGE_TEXT = "string"]

Description

The RESIGNAL statement is used like the SIGNAL statement but only in an exception handler to raise the specified condition to the closest outside handler for it from the nested blocks above the block containing the statement that caused the exception which invoked this handler. If no specific_condition is specified then the exception condition that invoked the handler which contains this RESIGNAL statement is passed out to the outside handler.

The MESSAGE_TEXT field of the GET DIAGNOSTICS area can be optionally SET to a text string value.

This implementation is a subset of the SQL standard.

Example

create procedure duptest(out stat int)
modifies sql data
begin
    declare stmt_name char(24);
    declare tractive smallint;
    declare scode char(20);
    declare sstate char(5);
    declare msgtext char(45);
    declare continue handler for sqlexception
    begin
        get diagnostics stmt_name = command_function, tractive = transaction_active;
        get diagnostics condition 1 scode = sqlcode_name, msgtext = message_text;
        set stat = 0;
    end;
    insert into acctmgr values "WAYNE", "Warren, Wayne", current_date(), 0.025;
    begin atomic
        declare exit handler for sqlcode eDUPLICATE
        begin
            set stat = 1;
            resignal;
        end;
        insert into acctmgr values "SCOTT", "Merilatt, Scott", current_date(), 0.025;
        insert into acctmgr values "RANDY", "Johnson, Randy", current_date(), 0.025;
        insert into acctmgr values "MARCO", "Rubio, Marco", current_date(), 0.025;
        set stat = 2;
    end;
    set stat = 3;
    select stat, stmt_name, tractive, scode, msgtext;
end;