RAISE ERROR

The RAISE ERROR statement causes an error to occur and causes the given error number to be put into the ALLBASE/SQL message buffer, together with the given error text. This statement is most useful within procedures invoked by rules, to cause the rule to fail and the statement firing the rule to have no effect. The effect of RAISE ERROR is to return with an error status; this statement can never "execute successfully."

Scope

ISQL or Application Programs

SQL Syntax

RAISE ERROR [ErrorNumber] [MESSAGE ErrorText]

Parameters

ErrorNumber

specifies the number of the error being raised. This can be any integer value. ErrorNumber has the following syntax:

{ Integer HostVariable ? :LocalVariable :ProcedureParameter }

The data type of the parameter, host variable, or local variable must be INTEGER or SMALLINT. The data type expected for the dynamic parameter is INTEGER.

If no ErrorNumber is given, 2350 is the default error number. The error range 7000 - 7999 is reserved for the RAISE ERROR statemnt. No ALLBASE/SQL errors are in this range.

Parameters and local variables may only be used within procedures. Host variables may only be used within embedded SQL. Dynamic parameters may only be used within dynamic SQL.

ErrorText

specifies text to be returned with the error. ErrorText has the following syntax:

{ 'CharacterString' :HostVariable ? :LocalVariable :ProcedureParameter }

The data type of the parameter, host, or local variable must be CHAR or VARCHAR. The data type expected for the dynamic parameter is CHAR(250). The value will be truncated to 250 bytes.

If no ErrorText is given, the default is an empty string.

Parameters and local variables are only used within procedures. Host variables are only used within embedded SQL. Dynamic parameters are only used within dynamic SQL.

Description

RAISE ERROR is for user-defined errors. The errors returned are application specific.
If ErrorNumber or ErrorText is NULL, an error is returned and the message is not generated.
ErrorNumber, if specified, must be greater than 0.
Execution of RAISE ERROR causes the number of the raised error to be placed in sqlcode and the RAISE ERROR text to be placed in the message buffer.
Since an error condition is the expected result of the statement, no corrective action need be taken except as directed by the application developer. Applications can use SQLEXPLAIN to fetch the text of the message and interpret it appropriately. Applications can also examine and/or display sqlcode.
You can use the DESCRIBE INPUT statement on this statement after you PREPARE it to show the number and characteristics of dynamic parameters, if any are used.

Authorization

Any user can issue this statement.

Examples

Example coded in a procedure to be invoked by a rule

   SELECT COUNT(*) INTO :rows FROM PurchDB.Orders 

       WHERE VendorNumber = :VendorNumber;

   IF :rows <> 0 THEN

       RAISE ERROR 1 MESSAGE 'Vendor number exists in the "Orders" table.';

   ENDIF;

Interactive example

   isql=> raise error 1 message 'This is error 1';

   This is error 1

   isql=>

Example using dynamic parameters
EXEC SQL PREPARE MyCmd from 'RAISE ERROR ? MESSAGE ?';
Accept values for error number and message text into host variables :ErrorNumber and :ErrorText, then execute the prepared command:
EXEC SQL EXECUTE MyCmd USING :ErrorNumber, :ErrorText;