Handle Error & Continue Execution of Next DML Statement in Hana Procedure

[kapil_kumar8]

Requirement -


Many times working in Hana Modelling we have to use Stored Procedures to perform multiple DML operation on a single or multiple tables. One scenario is that in stored procedures we need to perform multiple DML operation (Insert\Update) one after another.

Requirement is, if any of the DML operation\statement failed with some error then we need to handle that error, insert the error log in log table and continue with the execution of other DML statements.

Solution -

For the above requirement we can use CONTINUE handler code block. The behavior of the CONTINUE handler for catching and handling exceptions is the same as that of the EXIT handler used for error handling with the exception i.e. SQLScript execution continues with the statement following the exception.

Step 1 - Create two tables, one for DML operation (with Primary Key) and another for storing error code & error message.

-- Table Used for DML Operations

CREATE COLUMN TABLE XX.ERROR_TEST ("ID" INTEGER CS_INT, PRIMARY KEY ("ID")) UNLOAD PRIORITY 5 AUTO MERGE;



-- Table Used for Error Logs

CREATE COLUMN TABLE XX.ERROR_LOGS (ERROR_CODE INTEGER, ERROR_MSG NVARCHAR (5000));

Step 2 - Use Sql statements with CONTINUE handler code block as below.

DO BEGIN



	    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

	    

	    BEGIN

	    	INSERT INTO XX.ERROR_LOGS SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY; -- Insert Error Log into Log Table

		END;

    

		TRUNCATE TABLE XX.ERROR_TEST; 	-- Truncate Main Table

		TRUNCATE TABLE XX.ERROR_LOGS;	-- Truncate Log Table

		

		INSERT INTO XX.ERROR_TEST (ID) VALUES (1);

		COMMIT;

		INSERT INTO XX.ERROR_TEST (ID) VALUES (2);

		COMMIT;

		INSERT INTO XX.ERROR_TEST (ID) VALUES (3);

		COMMIT;

		INSERT INTO XX.ERROR_TEST (ID) VALUES (4);

		COMMIT;

		INSERT INTO XX.ERROR_TEST (ID) VALUES (1); -- Primary Key Violation Error

		COMMIT;

		INSERT INTO XX.ERROR_TEST (ID) VALUES (5);

		COMMIT;



    SELECT * FROM XX.ERROR_TEST;

    SELECT * FROM XX.ERROR_LOGS;

END;

In the above code block, 5th Insert statement (with insert value 1) will fail due to Primary Key violation. As we have added CONTINUE handler code block, execution of next statement will not stop and next insert statement will execute successfully.

Record with value 5 will be inserted into the table even there was error in the previous statement.

Main Table Output -

Error Log Table Output -

Please let me know the comments or suggestion on this.

Thanks.

Happy Learning!