RESIGNAL statement

The RESIGNAL statement is used within a condition handler to resignal the condition that activated the handler, or to raise an alternate condition so that it can be processed at a higher level. It causes an exception, warning, or not found condition to be returned along with optional message text.

Syntax

>>-+--------+--RESIGNAL----------------------------------------->
   '-label:-'             

>--+-------------------------------------------------------------------------------+-><
   |             .-VALUE-.                                                         |   
   '-+-SQLSTATE--+-------+--+-sqlstate-string-constant-+-+--+--------------------+-'   
     |                      '-+-SQL-variable-name--+---' |  '-signal-information-'     
     |                        '-SQL-parameter-name-'     |                             
     '-SQL-condition-name--------------------------------'

signal-information:


>>-SET--MESSAGE_TEXT--=--diagnostic-string-expression----------><

Description

label

Specifies the label for the RESIGNAL statement. A label name cannot be the same as the name of the SQL routine or another label within the same scope. For additional information, see References to labels.

SQLSTATE VALUE

Specifies the SQLSTATE that will be returned. Any valid SQLSTATE value can be used. It must be a character string constant with exactly five characters that follow the rules for SQLSTATE values:

Each character must be from the set of digits ('0' through '9') or non-accented upper case letter ('A' through 'Z').
The SQLSTATE class (the first two characters) cannot be '00' because it represents successful completion.

If the SQLSTATE does not conform to these rules, an error occurs.

sqlstate-string-constant

A character string constant with an actual length of five bytes that is a valid SQLSTATE value.

SQL-variable-name or SQL-parameter-name

Specifies an SQL variable or SQL parameter that is defined for the routine.

SQL-variable-name: Specifies an SQL variable that is declared within the compound-statement that contains the RESIGNAL statement or within a compound statement in which that compound statement is nested. SQL-variable-name must be defined as CHAR or VARCHAR data type with an actual length of five bytes, must not be null, and must contain a valid SQLSTATE value.
SQL_parameter-name: Specifies an SQL parameter that is defined for the routine that contains the SQLSTATE value. The SQL parameter must be defined as CHAR or VARCHAR data type with an actual length of five bytes, must not be null, and must contain a valid SQLSTATE value.

SQL-condition-name

Specifies the name of the condition that will be returned. SQL-condition-name must be declared within the compound-statement that contains the RESIGNAL statement, or within a compound statement in which that compound statement is nested.

SET MESSAGE_TEXT

Specifies a string that describes the error or warning. The string is returned in the SQLERRMC field of the SQLCA or with the GET DIAGNOSTICS statement.

diagnostic-string-expression: An expression with a data type of CHAR or VARCHAR that returns a character string of up to 1000 bytes that describes the error or warning condition. For information on how to obtain the complete message text, see GET DIAGNOSTICS.

Notes

While any valid SQLSTATE value can be used in the RESIGNAL statement, programmers should define new SQLSTATEs based on ranges reserved for applications. This practice prevents the unintentional use of an SQLSTATE value that might be defined by the database manager in a future release.

If the SQLSTATE or condition indicates that an exception is signaled (SQLSTATE class other than '01' or '02'):

If a condition handler exists in the same compound statement as the RESIGNAL statement, and the compound statement contains a condition handler for SQLEXCEPTION or the specified SQLSTATE or condition, the exception is handled and control is transferred to that condition handler.
If the compound statement is nested and an outer level compound statement has a condition handler for SQLEXCEPTION or the specified SQLSTATE or condition, the exception is handled and control is transferred to that condition handler.
Otherwise, the exception is not handled and control is immediately returned to the end of the compound statement.

If an SQLSTATE or a condition indicates that a warning or a not found condition is signaled:

If a condition handler exists in the same compound statement as the RESIGNAL statement, and the compound statement contains a condition handler for SQLWARNING, NOT FOUND, or the specified SQLSTATE or condition, the warning or not found condition is handled and control is transferred to that condition handler.
If the compound statement is nested and an outer level compound statement contains a condition handler for SQLWARNING, NOT FOUND, or the specified SQLSTATE or condition, the warning or not found condition is handled and control is returned to that condition handler.
Otherwise, the warning is not handled and processing continues with the next statement.

Considerations for the diagnostics area: The RESIGNAL statement might modify the contents of the current diagnostics area. If an SQLSTATE or condition-name is specified as part of the RESIGNAL statement, the RESIGNAL statement starts with a clear diagnostics area and sets the RETURNED_SQLSTATE to reflect the specified SQLSTATE or condition-name. If message text is specified, the MESSAGE_TEXT item of the condition area is assigned the specified value. DB2_RETURNED_SQLCODE is set to +438 or -438 corresponding to the specified SQLSTATE or condition-name.

Start of change Processing a RESIGNAL statement: If the RESIGNAL statement is specified without an SQLSTATE clause or a condition-name, the SQL routine resignals the identical condition that invoked the handler and the SQLCODE is not changed. End of change

When a RESIGNAL statement is issued and an SQLSTATE or condition-name is specified, the SQLCODE is based on he SQLSTATE value as follows:

If the specified SQLSTATE class is either '01' or ‘02', a warning or not found is signaled and the SQLCODE is set to +438.
Otherwise, an exception is returned and the SQLCODE is set to -438.

Examples

The following 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 to the client application.

CREATE PROCEDURE divide (IN numerator INTEGER,
        IN denominator INTEGER,
        OUT divide_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 divide_result = numerator / denominator;
   END IF;
  END