SQL Reference

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.

Syntax

                         .-VALUE-.
>>-SIGNAL----+-SQLSTATE--+-------+--sqlstate-string-constant--+->
             '-condition-name---------------------------------'
 
>-----+---------------------------------------------------------+-><
      '-SET--MESSAGE_TEXT-- = --+-variable-name--------------+--'
                                '-diagnostic-string-constant-'
 

Description

SQLSTATE VALUE sqlstate-string-constant

The specified string constant represents an SQLSTATE. It must be a character string constant with exactly 5 characters that 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 raised (SQLSTATE 428B3).

condition-name

Specifies the name of the condition. The condition name must be unique within the procedure and can only be referenced within the compound statement in which it is declared.

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. This clause can only be specified if a SQLSTATE or condition-name is also specified (SQLSTATE 42601).

variable-name

Identifies an SQL variable that must be declared within the compound statement. The SQL variable must be defined as a CHAR or VARCHAR data type.

diagnostic-string-constant

Specifies a character string constant that contains the message text.

Notes

• If a SIGNAL statement is issued, the SQLCODE that is assigned is:

  +438 if the SQLSTATE begins with '01' or '02'

  -438 otherwise

• If the SQLSTATE or condition indicates that an exception (SQLSTATE class other than '01' or '02') is signaled:
  • then the exception is handled and control is transferred to a handler, provided that a handler exists in the same compound statement (or an outer compound statement) as the signal statement, and the compound statement contains a handler for the specified SQLSTATE, condition-name, or SQLEXCEPTION;
  • 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 condition (SQLSTATE class '02') is signaled:
  • then the warning or not found condition is handled and control is transferred to a handler, provided that a handler exists in the same compound statement (or an outer compound statement) as the signal statement, and the compound statement contains a handler for the specified SQLSTATE, condition-name, SQLWARNING (if the SQLSTATE class is '01'), or NOT FOUND (if the SQLSTATE class is '02');
  • otherwise, the warning is not handled and processing continues with the next statement.
• SQLSTATE values are comprised 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.

Examples

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