Handle Error & Continue Execution of Next DML Statement in Hana Procedure
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.
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.