Handling SQL Errors

Go Up to Handling Errors

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. SQLCODE can also contain a warning status, such as when there are no more rows to retrieve in a FOR SELECT loop.

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

WHEN SQLCODE -803
DO
BEGIN
. . .

The following procedure includes a WHEN statement to handle SQLCODE -803 (attempt to insert a duplicate value in a UNIQUE key column). If the first column in TABLE1 is a UNIQUE key, and the value of parameter A is the same as one already in the table, then SQLCODE -803 is generated, and the WHEN statement sets an error message returned by the procedure.

CREATE PROCEDURE NUMBERPROC (A INTEGER, B INTEGER)

 RETURNS (E CHAR(60)) AS
BEGIN
BEGIN
INSERT INTO TABLE1 VALUES (:A, :B);
WHEN SQLCODE -803 DO
E = 'Error Attempting to Insert in TABLE1 - Duplicate Value.';
END;
END;!

For more information about SQLCODE, see the Language Reference.