HANA SQL- Exit Handler

Exception Handling in HANA SQL Script

In the recent last post HANA SQL- Default NULL , we came across a situation where a run time exception occurred and by adding a specific addition DEFAULT NULL, there is no more runtime exception and the execution proceeds to the next statement.

But there might be situation where if an exception occurred, we don’t want to continue but to handle the exception so we don’t receive dump and also inform the caller about the situation. In one of the last post HANA SQL – Error Codes where HANA DB engine assign a runtime code to the exception are shown.

In this post let’s handle the SQL exception.

Below is a AMDP procedure which is a dump/run time exception if the select statement fails.

Test program that calls the AMDP procedure.

Debug Details- One you try to execute the SELECT statement, the dump occurs.

We can see the AMDP exception and the SQL CODE- 1299 [ DATA NOT FOUND].

It is possible to do a TRY .. CATCH and ENDTRY in our report and call the AMDP method call in the TRY block, this is something still we can do and there would be no run time dump. But here the interest is inside the DB procedure how to handle the exception.

This can be done through the EXIT HANDLER addition in the DB procedure as highlighted below.

Let’s execute again and see the debug details-

Now when the SQL EXCEPTION occurred the control moved to the EXIT HANDLER section. We can see the SQL_ERROR_CODE and SQL_ERROR_MESSAGE.

The EXIT HANDLER section executes and we can do something here like filling a return message table with proper message to know about the situation to the caller.

One the EXIT HANDLER section finish its execution then it just returns from the method [ it doesn’t execute remaining part after the statement where the exception occurred].