DB2 Version 10.1 for Linux, UNIX, and Windows

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.

Invocation

This statement can only be embedded in a condition handler within a compound SQL (compiled) statement. The compound SQL (compiled) statement can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition.

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 diagram
>>-RESIGNAL----------------------------------------------------->

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

signal-information

|--SET--MESSAGE_TEXT-- = --+-SQL-variable-name----------+-------|
                           +-SQL-parameter-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).
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.
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.
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.
SQL-variable-name
Identifies an SQL variable, declared within the compound statement, that contains the message text.
SQL-parameter-name
Identifies an SQL parameter, defined for the routine, that contains the message text. The SQL parameter must be defined as a CHAR or VARCHAR data type.
diagnostic-string-constant
Specifies a character string constant that contains the message text.

Notes

Example

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