SIGNAL

The SIGNAL statement raises and signals an SQL event or exception condition.

Syntax

 ►── SIGNAL ──┬── SQLSTATE ──┬─────────┬───── 'sqlstate' ──────┬───────────────►
              │              └─ VALUE ─┘                       │
              └─ condition-name ───────────────────────────────┘

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

Parameters

'sqlstate': Specifies the value for SQLSTATE that is to be signaled. 'sqlstate' is a 5-character string-literal value 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 signaled. Condition-name must identify a condition defined by a condition declaration in a compound statement containing the SIGNAL 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

FLOW of CONTROL

If 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 activation of a handler.

If no handler is activated, control goes to the end of the compound statement that contains the signal. If the signal is not in a compound statement of an exit handler, control returns to the invoker of the SQL routine. Otherwise, it returns to the statement after the SIGNAL statement, just as if a continue handler had been activated.

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 that values are used in accordance with the classification of SQLSTATE values.

Note: For more information, see 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.TSIGNAL5
  ( TITLE     varchar(10) with default
  , RESULT    varchar(120)
  )
    EXTERNAL NAME TSIGNAL5 LANGUAGE SQL
Label_400:
 /*
 ** Trace execution of consecutive signal statements
 */

begin not atomic
  declare DEAD_LOCK condition for SQLSTATE '12000';
  declare NOT_FOUND condition for SQLSTATE '02000';

  declare continue HANDLER for SQLWARNING
    LABEL_9999:
      begin not atomic
        set RESULT = RESULT || ' Sqlwarning';
      end;
  declare continue handler for SQLEXCEPTION
    Label_8888:
      begin not atomic
        set RESULT = RESULT || ' Sqlexception';
      end;
  declare continue handler for SQLSTATE '23800'
        set RESULT = RESULT || ' 23800';
  declare continue handler for DEAD_LOCK
    LABEL_6666:
      begin not atomic
        set RESULT = RESULT || ' Deadlocked';
      end;
  declare continue handler for NOT FOUND
        set RESULT = RESULT || ' Not Found';
  set RESULT = 'Signal trace:';
  signal SQLSTATE '23800';
  signal NOT_FOUND;
  signal SQLSTATE '01200';
  signal SQLSTATE '72300';
  signal DEAD_LOCK;

end label_400
++
commit++
set options command delimiter default++
call user01.tsignal5('Signal');

*+
*+ TITLE
*+ -----
*+ Signal
*+
*+
*+ RESULT
*+ ------
*+ Signal trace: 23800 Not Found Sqlwarning Sqlexception
Deadlocked
*+