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.