RESIGNAL statement

The RESIGNAL statement is used within a condition handler to re-raise the current condition, 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.

Issuing the RESIGNAL statement without an operand causes the current condition to be passed upwards.

Syntax

>>-RESIGNAL----------------------------------------------------->

>--+-------------------------------------------------------------------------------+-><
   |             .-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

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 a 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 procedure.

SQL-variable-name: Specifies an SQL variable that is declared within the compound-statement that contains the RESIGNAL statement. SQL-variable-name must be defined as CHAR or VARCHAR data type with a 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 procedure that contains the SQLSTATE value. The SQL parameter must be defined as a CHAR or VARCHAR value and have a length of five bytes and must not be null. The SQL parameter must contain a valid SQLSTATE value.

SQL-condition-name

Specifies the name of the condition that will be returned. condition-name must be declared within the compound-statement.

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 SQLSTATE values 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 RESIGNAL statement is issued without an SQLSTATE clause or a condition-name, the RESIGNAL statement must be in a handler and the identical condition that activated the handler is returned. The SQLSTATE, SQLCODE, and the SQLCA associated with the condition are unchanged.

If an SQLSTATE clause or a condition-name was specified, 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 message is returned, and the SQLCODE is set to +438.
Otherwise, an exception is returned and the SQLCODE is set to -438.

The other fields of the SQLCA are set as follows:

SQLERRDx fields are set to zero.
SQLWARNx fields are set to blank.
SQLERRMC is set to the first 70 bytes of MESSAGE_TEXT.
SQLERRML is set to the length of SQLERRMC.
SQLERRP is set to ROUTINE.

When the SQLSTATE or condition indicates that an exception is returned (an SQLSTATE class other than '01' or '02'), the exception is not handled, and control is immediately returned to the end of the compound statement.

When the SQLSTATE or condition indicates that a warning (SQLSTATE class '02') is returned, the warning is not handled, and processing continues with the next statement.

When the SQLSTATE or condition indicates that a not-found condition (SQLSTATE class '02') is returned, the not-found condition is not handled, and processing continues with the next statement.

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