Examples of Error Behavior and Handling

From InterBase

Go Up to Handling Errors


When a procedure encounters an error – either a SQLCODE error, GDSCODE error, or user-defined exception – the statements since the last SUSPEND are undone.

SUSPEND should not be used in executable procedures. EXIT should be used to terminate the procedure. If this recommendation is followed, then when an executable procedure encounters an error, the entire procedure is undone. Since select procedures can have multiple SUSPEND statemets, possibly inside a loop statement, only the actions since the last SUSPEND are undone.

For example, here is a simple executable procedure that attempts to insert the same values twice into the PROJECT table. 

CREATE PROCEDURE NEW_PROJECT
(id CHAR(5), name VARCHAR(20), product VARCHAR(12))
RETURNS (result VARCHAR(80))
AS
BEGIN
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values inserted OK.';
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values Inserted Again.';
EXIT;
WHEN SQLCODE -803 DO
BEGIN
result = 'Could Not Insert Into Table - Duplicate Value';
EXIT;
END
END ;

This procedure can be invoked with a statement such as: 

EXECUTE PROCEDURE NEW_PROJECT 'XXX', 'Project X', 'N/A';

The second INSERT generates an error (SQLCODE -803, “invalid insert – no two rows can have duplicate values.”). The procedure returns the string, “Could Not Insert Into Table - Duplicate Value,” as specified in the WHEN clause, and the entire procedure is undone.

The next example is written as a select procedure, and invoked with the SELECT statement that follows it: 

. . . INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values inserted OK.';
SUSPEND;
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'Values Inserted Again.';
SUSPEND;
WHEN SQLCODE -803 DO
BEGIN
result = 'Could Not Insert Into Table - Duplicate Value';
EXIT;
END
SELECT * FROM SIMPLE('XXX', 'Project X', 'N/A');

The first INSERT is performed, and SUSPEND returns the result string, “Values Inserted OK.” The second INSERT generates the error because there have been no statements performed since the last SUSPEND, and no statements are undone. The WHEN statement returns the string, “Could Not Insert Into Table - Duplicate Value”, in addition to the previous result string.

The select procedure successfully performs the insert, while the executable procedure does not.

The next example is a more complex stored procedure that demonstrates ­SQLCODE error handling and exception handling. It is based on the previous example of a select procedure, and does the following:

  • Accepts a project ID, name, and product type, and ensures that the ID is in all capitals, and the product type is acceptable.
  • Inserts the new project data into the PROJECT table, and returns a string confirming the operation, or an error message saying the project is a duplicate.
  • Uses a FOR … SELECT loop with a correlated subquery to get the first three employees not assigned to any project and assign them to the new project using the ADD_EMP_PROJ procedure.
  • If the CEO employee number is selected, raises the exception, CEO, which is handled with a WHEN statement that assigns the CEO administrative assistant (employee number 28) instead to the new project.

Note that the exception, CEO, is handled within the FOR … SELECT loop, so that only the block containing the exception is undone, and the loop and procedure continue after the exception is raised. 

CREATE EXCEPTION CEO 'Can’t Assign CEO to Project.';
CREATE PROCEDURE NEW_PROJECT
(id CHAR(5), name VARCHAR(20), product VARCHAR(12))
RETURNS (result VARCHAR(30), num smallint)
AS
DECLARE VARIABLE emp_wo_proj smallint;
DECLARE VARIABLE i smallint;
BEGIN
id = UPPER(id); /* Project id must be in uppercase.
*/
INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)
VALUES (:id, :name, :product);
result = 'New Project Inserted OK.';
SUSPEND;
/* Add Employees to the new project */
i = 0;
result = 'Project Got Employee Number:';
FOR SELECT EMP_NO FROM EMPLOYEE
WHERE EMP_NO NOT IN (SELECT EMP_NO FROM EMPLOYEE_PROJECT)
INTO :emp_wo_proj
DO
BEGIN
IF (i < 3) THEN
BEGIN
IF (emp_wo_proj = 5) THEN
EXCEPTION CEO;
EXECUTE PROCEDURE ADD_EMP_PROJ :emp_wo_proj, :id;
num = emp_wo_proj;
SUSPEND;
END
ELSE
EXIT;
i = i + 1;
WHEN EXCEPTION CEO DO
BEGIN
EXECUTE PROCEDURE ADD_EMP_PROJ 28, :id;
num = 28;
SUSPEND;
END
END
/* Error Handling */
WHEN SQLCODE -625 DO
BEGIN
IF ((:product <> 'software') OR (:product <> 'hardware') OR
(:product <> 'other') OR (:product <> 'N/A')) THEN
result = 'Enter product: software, hardware, other, or N/A';
END
WHEN SQLCODE -803 DO
result = 'Could not insert into table - Duplicate Value';
END ;

This procedure can be called with a statement such as: 

SELECT * FROM NEW_PROJECT('XYZ', 'Alpha project', 'software');

With results such as the following: 

RESULT                          NUM
===========================     ======
New Project Inserted OK.        <null>
Project Got Employee Number:    28
Project Got Employee Number:    29
Project Got Employee Number:    36

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Examples_of_Error_Behavior_and_Handling&oldid=31774"