The SIGNAL statement signals an error or warning condition.
It causes an error or warning to be returned with the specified SQLSTATE
and optional condition-information-items. The syntax of SIGNAL
in an SQL function, SQL procedure, or SQL trigger is a similar to
what is supported as a SIGNAL statement in other contexts.
Syntax

.-VALUE-.
>>-+--------+--SIGNAL--+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+-->
'-label:-' | '-sqlstate-string-variable-' |
'-SQL-condition-name--------------------------------'
>--+--------------------+--------------------------------------><
'-signal-information-'
signal-information
.-,-----------------------------------------------------------.
V |
|--+-SET----+-MESSAGE_TEXT-------+-- = --+-SQL-variable-name----------+-+-+--|
| +-CONSTRAINT_CATALOG-+ +-SQL-parameter-name---------+ |
| +-CONSTRAINT_SCHEMA--+ '-diagnostic-string-constant-' |
| +-CONSTRAINT_NAME----+ |
| +-CATALOG_NAME-------+ |
| +-SCHEMA_NAME--------+ |
| +-TABLE_NAME---------+ |
| +-COLUMN_NAME--------+ |
| +-CURSOR_NAME--------+ |
| +-CLASS_ORIGIN-------+ |
| '-SUBCLASS_ORIGIN----' |
'-(--diagnostic-string-constant--)-------------------------------------'
Description
- label
- Specifies the label for the SIGNAL statement. The label name cannot
be the same as the routine name or another label within the same scope.
For more information, see References to SQL labels.
- SQLSTATE VALUE
- Specifies the SQLSTATE that will be signalled. The specified value
must not be null and must follow the rules for SQLSTATEs:
- Each character must be from the set of digits ('0' through '9')
or non-accented upper case letters ('A' through 'Z').
- The SQLSTATE class (first two characters) cannot be '00' since
this represents successful completion.
If the SQLSTATE does not conform to these rules, an error
is returned.
- sqlstate-string-constant
- The sqlstate-string-constant must be a character string
constant with exactly 5 characters.
- sqlstate-string-variable
- The sqlstate-string-variable must be a character or Unicode
graphic variable. The actual length of the contents of the variable must
be 5.
- SQL-condition-name
- Specifies the name of the condition that will be signalled. The SQL-condition-name must
be declared within the compound-statement.
- SET
- Introduces the assignment of values to condition-information-items.
The condition-information-item values can be accessed using
the GET DIAGNOSTICS statement. The only condition-information-item that
can be accessed in the SQLCA is MESSAGE_TEXT.
- MESSAGE_TEXT
- Specifies a string that describes the error or warning.
If
an SQLCA is used,
- the string is returned in the SQLERRMC field of the SQLCA
- if the actual length of the string is longer than 1000 bytes,
it is truncated without a warning.
- CONSTRAINT_CATALOG
- Specifies a string that indicates the name of the database that
contains a constraint related to the signalled error or warning.
- CONSTRAINT_SCHEMA
- Specifies a string that indicates the name of the schema that
contains a constraint related to the signalled error or warning.
- CONSTRAINT_NAME
- Specifies a string that indicates the name of a constraint related
to the signalled error or warning.
- CATALOG_NAME
- Specifies a string that indicates the name of the database that
contains a table or view related to the signalled error or warning.
- SCHEMA_NAME
- Specifies a string that indicates the name of the schema that
contains a table or view related to the signalled error or warning.
- TABLE_NAME
- Specifies a string that indicates the name of a table or view
related to the signalled error or warning.
- COLUMN_NAME
- Specifies a string that indicates the name of a column in the
table or view related to the signalled error or warning.
- CURSOR_NAME
- Specifies a string that indicates the name of a cursor related
to the signalled error or warning.
- CLASS_ORIGIN
- Specifies
a string that indicates the origin of the SQLSTATE class related to
the signalled error or warning.
- SUBCLASS_ORIGIN
- Specifies
a string that indicates the origin of the SQLSTATE subclass related
to the signalled error or warning.
- SQL-variable-name
- Identifies an SQL variable declared within the compound-statement,
that contains the value to be assigned to the condition-information-item.
The SQL variable must be defined as CHAR, VARCHAR, Unicode GRAPHIC,
or Unicode VARGRAPHIC variable.
- SQL-parameter-name
- Identifies an SQL parameter declared within the compound-statement,
that contains the value to be assigned to the condition-information-item.
The SQL parameter must be defined as CHAR, VARCHAR, Unicode GRAPHIC,
or Unicode VARGRAPHIC variable.
- diagnostic-string-constant
- Specifies a character string constant that contains the value
to be assigned to the condition-information-item.
- ( diagnostic-string-constant )
- Specifies a character string constant that contains the message
text.
This form is only allowed in the triggered action of a CREATE
TRIGGER statement.
To conform with the ANS and ISO standards,
this form should not be used. It is provided for compatibility with
other products.
Notes
SQLSTATE
values: Any valid SQLSTATE value can be used in the SIGNAL statement.
However, it is recommended that programmers define new SQLSTATEs based
on ranges reserved for applications. This prevents the unintentional
use of an SQLSTATE value that might be defined by the database manager
in a future release.
Assignment: When the SIGNAL statement is executed,
the value of each of the specified string-constants and variables is
assigned to the corresponding condition-information-item. However,
if the length of a string-constant or variable is longer
than the maximum length of the corresponding condition-information-item,
it is truncated without a warning. For details on the assignment rules,
see Assignments and comparisons. For details on the maximum
length of specific condition-information-items, see GET DIAGNOSTICS.
Processing a SIGNAL statement: When a SIGNAL statement
is issued, the SQLCODE returned in the SQLCA is based on the SQLSTATE
value as follows:
- If the specified SQLSTATE class is either '01' or '02', a warning
or not found is signalled and the SQLCODE is set to +438.
- Otherwise, an exception is signalled and the SQLCODE is set to
–438.
If the SQLSTATE or condition indicates that an exception
(SQLSTATE class other than '01' or '02') is signalled,
- If a handler exists in the same compound statement as the SIGNAL
statement, and the compound statement contains a handler for SQLEXCEPTION
or the specified SQLSTATE or condition; the exception is handled and
control is transferred to that handler.
- If the compound-statement is nested and an outer level compound-statement has
a handler for SQLEXCEPTION or the specified SQLSTATE or condition;
the exception is handled and control is transferred to that handler.
- Otherwise, the exception is not handled and control is immediately
returned to the end of the compound statement.
If the SQLSTATE or condition indicates that a warning
(SQLSTATE class '01') or not found (SQLSTATE class '02') is signalled,
- If a handler exists in the same compound statement as the SIGNAL
statement, and the compound statement contains a handler for SQLWARNING
(if the SQLSTATE class is '01'), NOT FOUND (if the SQLSTATE class
is '02'), or the specified SQLSTATE or condition; the warning or not
found condition is handled and control is transferred to that handler.
- If the compound-statement is nested and an outer level
compound statement contains a handler for SQLWARNING (if the SQLSTATE
class is '01'), NOT FOUND (if the SQLSTATE class is '02'), or the
specified SQLSTATE or condition; the warning or not found condition
is handled and the exception is handled and control is returned to
that handler.
- Otherwise, the warning is not handled and processing continues
with the next statement.
Considerations for the diagnostics area: The SIGNAL
statement starts with a clear of the diagnostics area and then sets
the RETURNED_SQLSTATE to reflect the specified SQLSTATE or SQL-condition-name.
If any signal-information is specified, the corresponding
items in the condition area are assigned the specified values. DB2_RETURNED_SQLCODE
is set to +438 or -438 corresponding to the specified SQLSTATE or SQL-condition-name.
Example
An SQL procedure for an order system
that signals an application error when a customer number is not known
to the application. The ORDERS table includes a foreign key to the
CUSTOMER table, requiring that the CUSTNO exist before an order can
be inserted.
CREATE PROCEDURE SUBMIT_ORDER
(IN ONUM INTEGER, IN CNUM INTEGER,
IN PNUM INTEGER, IN QNUM INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
SIGNAL SQLSTATE '75002'
SET MESSAGE_TEXT = 'Customer number is not known';
INSERT INTO ORDERS (ORDERNO, CUSTNO, PARTNO, QUANTITY)
VALUES (ONUM, CNUM, PNUM, QNUM);
END