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

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!