WHEN...DO

Go Up to Procedures and Triggers

Error-handling statement that performs the statements following DO when the specified error occurs. Available in triggers and stored procedures.

WHEN {<error> [, <error> …] | ANY}
DO <<compound_statement>>

<error>=
{EXCEPTION exception_name | SQLCODE number | GDSCODE errcode}

Argument	Description
EXCEPTION <exception_name>	The name of an exception already in the database
SQLCODE <number>	A SQLCODE error code number
GDSCODE <errcode>	An InterBase error code. Use Table 5.5 and strip isc_ before mentioning the error code with GDSCODE usage. For example: GDSCODE lock_conflict.
ANY	Keyword that handles any of the above types of errors.
<compound_statement>	Statement or block executed when any of the specified errors occur.

Important:
If used, WHEN must be the last statement in a BEGIN…END block. It should come after SUSPEND, if present.

Description: Procedures can handle three kinds of errors with a WHEN statement:

• Exceptions raised by EXCEPTION statements in the current procedure, in a nested procedure, or in a trigger fired as a result of actions by such a procedure.
• SQL errors reported in SQLCODE.
• InterBase error codes.

The WHEN ANY statement handles any of the three types.

Handling Exceptions (WHEN … DO)

Instead of terminating when an exception occurs, a procedure can respond to and perhaps correct the error condition by handling the exception. When an exception is raised, it:

• Terminates execution of the BEGIN … END block containing the exception and undoes any actions performed in the block.
• Backs out one level to the next BEGIN … END block and seeks an exception-handling (WHEN) statement, and continues backing out levels until one is found. If no WHEN statement is found, the procedure is terminated and all its actions are undone.
• Performs the ensuing statement or block of statements specified after WHEN, if found.
• Returns program control to the block or statement in the procedure following the WHEN statement.

Note:
An exception that is handled with WHEN does not return an error message.

Handling SQL Errors (WHEN … DO)

Procedures can also handle error numbers returned in SQLCODE. After each SQL statement executes, SQLCODE contains a status code indicating the success or failure of the statement. It can also contain a warning status, such as when there are no more rows to retrieve in a FOR SELECT loop.

Handling InterBase Error Codes

Procedures can also handle InterBase error codes. For example, suppose a statement in a procedure attempts to update a row already updated by another transaction, but not yet committed. In this case, the procedure might receive an InterBase error code, isc_lock_conflict. Perhaps if the procedure retries its update, the other transaction may have rolled back its changes and released its locks. By using a WHEN GDSCODE statement, the procedure can handle lock conflict errors and retry its operation.

Example: For example, if a procedure attempts to insert a duplicate value into a column defined as a PRIMARY KEY, InterBase will return SQLCODE -803. This error can be handled in a procedure with the following statement:

WHEN SQLCODE -803
DO
BEGIN
. . .

For example, the following procedure, from an isql script, includes a WHEN statement to handle errors that may occur as the procedure runs. If an error occurs and SQLCODE is as expected, the procedure continues with the new value of B. If not, the procedure cannot handle the error, and rolls back all actions of the procedure, returning the active SQLCODE.

CREATE PROCEDURE NUMBERPROC (A INTEGER) RETURNS (B INTEGER) AS
BEGIN
B = 0;
BEGIN
UPDATE R SET F1 = F1 + :A;
UPDATE R SET F2 = F2 * F2;
UPDATE R SET F1 = F1 + :A;
WHEN SQLCODE -803 DO
B = 1;
END
EXIT;
END;

See Also

• EXCEPTION

For more information about InterBase error codes and SQLCODE values, see Error Codes and Messages.

Advance To:

• WHILE … DO