Control Statements › RESIGNAL

RESIGNAL

The RESIGNAL statement resignals an SQL event or exception condition in a handler for the next higher level scope.

Syntax

►── RESIGNAL ─┬────────────────────────────────────────────────┬───────────────►
              ├── SQLSTATE ──┬─────────┬─── 'sqlstate' ────────┤
              │              └─ VALUE ─┘                       │
              └─ condition-name ───────────────────────────────┘

►──┬───────────────────────────────────────────────────────────┬──────────────►◄
   └── SET MESSAGE_TEXT ── = ─┬────────────────────────────────│
                              └─ simple-value-specification ───┘

Parameters

'sqlstate'

Specifies the value for SQLSTATE that is to be resignaled. 'Sqlstate' is a 5-character string-literal that consists of only digits (0-9) and capital alphabetic characters (A-Z). 'Sqlstate' cannot be '00000', the value of SQLSTATE for successful completion.

condition-name

Specifies the name of a condition whose SQLSTATE value is to be resignaled. Condition-name must identify a condition defined by a condition-declaration in a compound-statement containing the RESIGNAL statement. if more than one such condition-declaration has the specified condition-name, the one with the innermost scope is raised.

simple-value-specification

Specifies a character value to be added to the information item MESSAGE-TEXT. simple-value-specification must have a character data type.

Usage

Propagating the SQL Condition

The RESIGNAL statement can only be used in a handler to propagate an SQL condition to the scope that encloses the exception handler's scope. If the RESIGNAL is issued in a handler of a top level compound statement, control returns to the invoker of the SQL-invoked routine.

FLOW of CONTROL

If in the outer scope a handler exists for the raised exception or SQL event, the handler acquires control. After execution of the handler, control returns as with any other statement that causes a handler to activate.

SQLSTATE

There are no restrictions on the values that can be set for SQLSTATE, other than compliance with the syntactic rules for SQLSTATE values. We recommend using values in accordance with the classification of SQLSTATE values.

MESSAGE_TEXT

This is an information item of character type with a length of 80.

Example

set options command delimiter '++';
create procedure USER01.RESIGNAL1
  ( TITLE     varchar(10) with default
  , RESULT    varchar(120)
  )
    EXTERNAL NAME RESIGNA1 LANGUAGE SQL
Label_400:
 /*
 ** Resignal show case
 */

begin not atomic
  declare DEAD_LOCK condition for SQLSTATE '12000';
  declare NOT_FOUND condition for SQLSTATE '02000';
  declare exit handler for NOT FOUND
    begin not atomic
        set RESULT = RESULT || ' Not Found';
        resignal SQLSTATE '38607';
    end;

  set RESULT = 'Signal trace:';
  signal NOT_FOUND;

end label_400
++
commit;
set options command delimiter++

call user01.resignal1('Signal');

*+ Status = -4       SQLSTATE = 38000        Messages follow:
*+ DB001075 C-4M321: Procedure RESIGNA1 exception 38607