SIGNAL explicitly reports, in a routine, anexception or a self-defined SQLSTATE.
SIGNAL deletes the current diagnostics area and enters corresponding diagnostic information into the current diagnostics area:
SIGNAL is one of the diagnostic statements. Detailed information on the use and effect of SIGNAL can be found in section "Diagnostic information in routines".
SIGNAL { 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".
MESSAGE_TEXT=alphanumeric_literal
Any information (maximum length: 120 characters). The text length is entered in MESSAGE_LENGTH and MESSAGE_OCTET_LENGTH.
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 is supplied with the corresponding NULL values.
Examples (see also "Diagnostic information in routines" )
Reporting a self-defined SQLSTATE:
SIGNAL SQLSTATE VALUE '46SA5';
Reporting a condition with information text:
SIGNAL end_job SET MESSAGE_TEXT='The end is near!';
See also
COMPOUND, CREATE FUNCTION, CREATE PROCEDURE, GET DIAGNOSTICS, RESIGNAL