RAISE_ERROR scalar function

The RAISE_ERROR function causes the statement that invokes the function to return an error with the specified SQLSTATE (along with SQLCODE -438) and error condition. The RAISE_ERROR function always returns the null value with an undefined data type.

The schema is SYSIBM.

sqlstate

An expression that returns a character string (CHAR or VARCHAR) of exactly 5 characters.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR(5) data type.

The sqlstate value must follow these rules for application-defined 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', '01', or '02' because these are not error classes.
If the SQLSTATE class (first two characters) starts with the character '0' through '6' or 'A' through 'H', the subclass (last three characters) must start with a letter in the range 'I' through 'Z'.
If the SQLSTATE class (first two characters) starts with the character '7', '8', '9', or 'I' though 'Z', the subclass (last three characters) can be any of '0' through '9' or 'A through 'Z'.

diagnostic-string

An expression that returns a character string with a data type of CHAR or VARCHAR and a length of up to 70 bytes. The string contains EBCDIC data that describes the error condition. If the string is longer than 70 bytes, it is truncated.

Since the data type of the result of RAISE_ERROR is undefined, it can only be used in a SET host-variable or SQL procedure languageassignment-statement. To use this function in another context, such as alone in a select list, you must use a cast specification to give a data type to the null value that is returned. The RAISE_ERROR function is most useful with CASE expressions.

Example: For each employee in sample table DSN8D10.EMP, list the employee number and education level. List the education level as 'Post Graduate', 'Graduate' and 'Diploma' instead of the integer that it is stored as in the table. If an education level is greater than '20', raise an error ('70001') with a description.

   SELECT EMPNO,
     CASE WHEN EDLEVEL < 16 THEN 'Diploma'
          WHEN EDLEVEL < 18 THEN 'Graduate'
          WHEN EDLEVEL < 21 THEN 'Post Graduate'
          ELSE RAISE_ERROR('70001',
                           'EDUCLVL has a value greater than 20')
     END
     FROM DSN8D10.EMP;