SAP HANA: User-Defined Exception Handling

[former_member182302]

Hi Folks,

I was going through a basic requirement which i received today and thought of sharing the same with you.

Problem description:

If we have any select on a table which has ‘0’ records, instead of not displaying any data in the output we need to display “No Data Found” as the Output

/* Creating a Procedure for testing User-Defined Exception Handling */

CREATE PROCEDURE EXCEPTION_HANDLING AS BEGIN

DECLARE CountVar INT; /* Variable to count number of records in the table */
DECLARE CUSTOMCONDITION CONDITION FOR SQL_ERROR_CODE 10001;/* Custom Error Code = 10001*/

/*User Defined exception handler */

DECLARE EXIT HANDLER FOR CUSTOMCONDITION

SELECT ::SQL_ERROR_CODE AS "Error Code", ::SQL_ERROR_MESSAGE AS "Error Message" FROM DUMMY;

/* To check if the count = 0 to raise “no data found exception” */

SELECT COUNT (*) INTO CountVar FROM EXCEPTION_TEST;

IF CountVar  = 0

THEN
/* Signaling Custom error and setting the Custom Error Message */
SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = 'No Data Found Exception';

END IF;

END;

CALL EXCEPTION_HANDLING;

/*OUTPUT which shows the userdefined error code along with userdefined error message as shown below*/

**Note:Please note that i have used the above example only to illustrate the "User Defined Exception" with the example.

The best way to check if the table is empty or not is by using "Exists" clause like the below statement:

SELECT 1 INTO CountVar FROM DUMMY WHERE EXISTS (SELECT 'X' FROM "EXCEPTION_TEST");

which if we use and if table is "Empty" the exception gets caught by SQLEXCEPTION with error code = 1299

Do let me know your views and let me know any-other better alternate ways which you might have used.

Your's

Krishna Tangudu :smile: