SIGNAL statement

The SIGNAL statement is used to signal an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text.

Invocation

This statement can be embedded in an:
  • SQL procedure definition
  • Compound SQL (compiled) statement
  • Compound SQL (inlined) statement
The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

If a module condition is referenced, the privileges held by the authorization ID of the statement must include EXECUTE privilege on the module.

Syntax

Read syntax diagramSkip visual syntax diagramSIGNALSQLSTATEVALUEsqlstate-string-constantsqlstate-string-variablecondition-namesignal-information
signal-information
Read syntax diagramSkip visual syntax diagramSET MESSAGE_TEXT = diagnostic-string-expression(diagnostic-string-expression)

Description

SQLSTATE VALUE
Specifies the SQLSTATE that will be returned. Any valid SQLSTATE value can be used. The specified value must follow the rules for SQLSTATEs:
  • Each character must be from the set of digits (0 through 9) or upper case letters (A through Z) without diacritical marks
  • The SQLSTATE class (first two characters) cannot be 00, since this represents successful completion.
In the context of a compound SQL (inlined) statement, a MERGE statement, or as the only statement in a trigger body, the following rules must also be applied:
  • The SQLSTATE class (first two characters) cannot be 01 or 02, since these are not error classes.
  • If the SQLSTATE class starts with the numbers 0 through 6 or the letters A through H, then the subclass (the last three characters) must start with a letter in the range of I through Z.
  • If the SQLSTATE class starts with the numbers 7, 8, 9, or the letters I through Z, then the subclass can be any of 0 through 9 or A through Z.
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 specified SQL variable or SQL parameter must be of data type CHAR(5) and must not be the null value.
condition-name
Specifies the name of a condition that will be returned. The condition-name must be declared within the compound-statement or identify a condition that exists at the current server (SQLSTATE 42373).
SET MESSAGE_TEXT =
Specifies a string that describes the error or warning. The string is returned in the SQLERRMC field of the SQLCA. If the actual string is longer than 70 bytes, it is truncated without warning.
diagnostic-string-expression
A literal string, or a local variable or parameter that describes the error condition. If the string is longer than 70 bytes, it is truncated.
(diagnostic-string-expression)
An expression of type CHAR or VARCHAR that returns a character string of up to 70 bytes to describe the error condition. If the string is longer than 70 bytes, it is truncated. Regular use of this option is not recommended.

Notes

  • If a SIGNAL statement is issued using a condition-name that has no associated SQLSTATE value and the condition is not handled, SQLSTATE 45000 is returned and the SQLCODE is set to -438. Note that such a condition will not be handled by a condition handler for SQLSTATE 45000 that is within the scope of the routine issuing the SIGNAL statement.
  • If a SIGNAL statement is issued using an SQLSTATE value or a condition-name with an associated SQLSTATE value, the SQLCODE returned is based on the SQLSTATE value as follows:
    • If the specified SQLSTATE class is either 01 or 02, a warning or not found condition is returned and the SQLCODE is set to +438.
    • Otherwise, an exception condition is returned and the SQLCODE is set to -438.
  • A SIGNAL statement has the indicated fields of the SQLCA set as follows:
    • sqlerrd fields are set to zero
    • sqlwarn fields are set to blank
    • sqlerrmc is set to the first 70 bytes of MESSAGE_TEXT
    • sqlerrml is set to the length of sqlerrmc, or to zero if no SET MESSAGE_TEXT clause is specified
    • sqlerrp is set to ROUTINE
  • SQLSTATE values are composed of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful execution conditions.
    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.
    • SQLSTATE classes that begin with the characters 7 through 9, or I through Z may be defined. Within these classes, any subclass may be defined.
    • SQLSTATE classes that begin with the characters 0 through 6, or A through H are reserved for the database manager. Within these classes, subclasses that begin with the characters 0 through H are reserved for the database manager. Subclasses that begin with the characters I through Z may be defined.

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)
      SPECIFIC SUBMIT_ORDER
      MODIFIES SQL DATA
     LANGUAGE SQL
     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