RESIGNAL explicitly reports an exception or an SQLSTATE in a local exception routine. In contrast to SIGNAL, the specification of an exception name or SQLSTATE is optional.
RESIGNAL uses the diagnostics area of the SQL statement which has activated the exception routine as the current diagnostics area, and enters corresponding diagnostic information in the current diagnostics area.
RESIGNAL is one of the diagnostic statements. Detailed information on the use and effect of RESIGNAL can be found in section "Diagnostic information in routines".
RESIGNAL [ error_name | sqlstate ] [SET diagnostic_info ]
sqlstate ::= SQLSTATE [VALUE] alphanumeric_literal
diagnostic_info ::= MESSAGE_TEXT= message
message ::={ alphanumeric_literal | local_variable | routine_parameter }
exception_name
Name of an exception or SQLSTATE. exception_name is defined in the local data of a routine, see "Local data".
sqlstate
Explicit specification of a self-defined SQLSTATE (alphanumeric literal with the length 5), see section "Self-defined SQLSTATEs".
exception_name and sql_state not specified:
The diagnostic information CONDITION_IDENTIFIER and RETURNED_SQLSTATE remains unchanged.
MESSAGE_TEXT=alphanumeric_literal
Any information (maximum length: 120 characters).
MESSAGE_TEXT=local_variable / routine_parameter
The value of the local variable or of the specified routine parameter is entered as information text.
The data type of local_variable / routine_parameter must be compatible with the data type VARCHAR(120). The rules in section "Entering values in a procedure parameter (output) or local variable" apply. The text length is entered in MESSAGE_LENGTH and MESSAGE_OCTET_LENGTH.
SET MESSAGE TEXT omitted:
The diagnostic information MESSAGE_TEXT, MESSAGE_LENGTH and MESSAGE_OCTET_LENGTH remains unchanged.
Examples (see also "Diagnostic information in routines" )
Reporting a condition with information text:
RESIGNAL SET MESSAGE_TEXT='The end is near!';
See also
COMPOUND, CREATE FUNCTION, CREATE PROCEDURE, GET DIAGNOSTICS, SIGNAL