Exceptional handling in DB2 stored procedure

 In DB2, you can use the HANDLER statement to handle exceptions that occur within a stored procedure.

Here is an example of how to use the HANDLER statement in a stored procedure:

CREATE OR REPLACE PROCEDURE myProcedure (IN id INTEGER) BEGIN DECLARE myException CONDITION FOR SQLSTATE '45000'; DECLARE CONTINUE HANDLER FOR myException BEGIN -- code to handle the exception goes here SELECT 'An error occurred: ' || SQLERRMC; END; -- code for your stored procedure goes here DELETE FROM myTable WHERE id = id; END;

 Example -1 for insert operation

CREATE PROCEDURE InsertData() BEGIN DECLARE SQLCODE INT; DECLARE SQLSTATE CHAR(5); DECLARE EXIT HANDLER FOR SQLSTATE '23505' -- Handle unique constraint violation exception BEGIN SET SQLCODE = 100; SET SQLSTATE = '00000'; END; BEGIN INSERT INTO ExampleTable (column1, column2) VALUES (1, 'value1'); END; END;

Example -2 update operation

CREATE PROCEDURE UpdateData() BEGIN DECLARE SQLCODE INT; DECLARE SQLSTATE CHAR(5); DECLARE EXIT HANDLER FOR SQLSTATE '02000' -- Handle division by zero exception BEGIN SET SQLCODE = 100; SET SQLSTATE = '00000'; END; BEGIN UPDATE ExampleTable SET column2 = column2 / 0; -- Raises division by zero exception END; END;