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 or EXECUTEIN privilege or DATAACCESS on the schema containing the module.

Syntax

Read syntax diagramSkip visual syntax diagramRESIGNALSQLSTATEVALUEsqlstate-string-constantsqlstate-string-variablecondition-namesignal-information
signal-information
Read syntax diagramSkip visual syntax diagramSETMESSAGE_TEXT =  SQL-variable-nameSQL-parameter-namediagnostic-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

  • If a RESIGNAL statement is issued without specifying an SQLSTATE clause or a condition-name, the identical condition that invoked the handler is returned. The SQLSTATE, SQLCODE and the SQLCA associated with the condition are unchanged.
  • If a RESIGNAL statement is issued using a condition-name that has no associated SQLSTATE value and the condition is not handled, SQLSTATE 45000 is returned and the SQLCODE is set to -438. Note that such a condition will not be handled by a condition handler for SQLSTATE 45000 that is within the scope of the routine issuing the RESIGNAL statement.
  • If a RESIGNAL statement is issued using an SQLSTATE value or a condition-name with an associated SQLSTATE value, 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 condition is returned and the SQLCODE is set to +438.
    • Otherwise, an exception condition is returned and the SQLCODE is set to -438.
  • A RESIGNAL statement has the indicated fields of the SQLCA set as follows:
    • sqlerrd fields are set to zero
    • sqlwarn fields are set to blank
    • sqlerrmc is set to the first 70 bytes of MESSAGE_TEXT
    • sqlerrml is set to the length of sqlerrmc, or to zero if no SET MESSAGE_TEXT clause is specified
    • sqlerrp is set to ROUTINE
  • Refer to the Notes section under SIGNAL statement for further information about SQLSTATE values.

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