Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
kapil_kumar8
Explorer
0 Kudos
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!

 

 

 
2 Comments
Labels in this area