SET TERM !!;
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!!
SET TERM ;!!

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');