SQL Reference

RESIGNAL Statement

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

Syntax

>>-RESIGNAL----+----------------------------------------+------><
               '-| signal-value |--+-----------------+--'
                                   '-| signal-info |-'
 
signal-value
 
                .-VALUE-.
|---+-SQLSTATE--+-------+--sqlstate-string-constant--+----------|
    '-condition-name---------------------------------'
 
signal-info
 
|---+---------------------------------------------------------+-|
    '-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.

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 an 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 the RESIGNAL statement is specified without a SQLSTATE clause or a condition-name, the SQL routine returns to the caller with the identical condition that invoked the handler.
• If a RESIGNAL statement is issued, and specifies a SQLSTATE or condition-name, the SQLCODE assigned is:

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

  -438 otherwise

  When a RESIGNAL statement is issued with no options, the SQLCODE is unchanged from the exception that caused the handler to be invoked.
• If the SQLSTATE or condition indicates that an exception is signalled (an SQLSTATE class other than '01' or '02'):
  • then, the exception is handled and control is transferred to a handler, provided that a handler exists in the next outer compound statement (or a compound statement even further out) from the compound statement that includes the handler with the resignal 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 signalled:
  • then the warning or not found condition is handled and control is transferred to a handler, provided that a handler exists in the next outer compound statement (or a compound statement even further out) from the compound statement that includes the handler with the resignal 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.

Examples

This example detects a division by zero error. The IF statement uses a SIGNAL statement to invoke the overflow condition handler. The condition handler uses a RESIGNAL statement to return a different SQLSTATE value to the client application.

   CREATE PROCEDURE divide ( IN numerator INTEGER
                               IN denominator INTEGER
                               OUT result INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   BEGIN
     DECLARE overflow CONDITION FOR SQLSTATE '22003';
       DECLARE CONTINUE HANDLER FOR overflow
       RESIGNAL SQLSTATE'22375' ;
       IF denominator = 0 THEN
       SIGNAL overflow;
     ELSE
        SET result = numerator / denominator;
     END IF;
   END