Skip to Content
Author's profile photo Krishna Tangudu

SAP HANA: User-Defined Exception Handling

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*/


Screen Shot 2014-01-15 at 10.09.07 PM.png



**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


Screen Shot 2014-01-15 at 10.13.48 PM.png


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


Your’s

Krishna Tangudu 🙂

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo vijaykumar ijeri
      vijaykumar ijeri

      Nice Krishna!

      Regards,

      Vijay

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog Post Author

      Thank you Vijay 🙂 .

      Regards,

      Krishna Tangudu

      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      Really nice idea Krishna.

      I am just a novice when it comess to HANA. There are two things I need some more clarity,

      1) I dont understand the use of DUMMY table here. When I do a SELECT * in my system it just returns one row

      2) Is 10001 a standard error code that is returned for an empty table.

      Please answer these if you can spare  the time.

      Thanks,

      Benedict

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog Post Author

      Hi Vasanth,

      Thanks for your feedback 🙂

      1) Dummy is very similar to Dual table in oracle. It is helpful when you want to view the results of system functions

      ex: select current_timestamp from dummy


      2) 10001 is a custom error code the exception am dealing here is a user defined exception.

      We can start declaring customer error codes from 10001.

      On the other hand the system generated error code for "No data found" exception is 1299

      Regards,

      Krishna Tangudu

      Author's profile photo Rama Shankar
      Rama Shankar

      Good blog Krishna. Thanks!

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog Post Author

      Hey Rama Shankar,

      Thanks 🙂

      Author's profile photo Former Member
      Former Member

      Hi Krishna,

      Good one. Thank you.

      Can you clear my doubt please?

      In this specific example...

      Can't we show user defined message like below.


      IF CountVar  = 0

      THEN

      SELECT  'No Data Found Exception' FROM DUMMY;

      END IF;

      If the above code works fine, then what is the use of user defined exception handling.

      Could you please elaborate more on this or give one more example, how specifically user defined exception handling helps us.

      Please let me know if my doubt is correct ?

      Thanks,

      Sree

      Author's profile photo Former Member
      Former Member

      Hi Krishna,

      I was looking for exception handling and I found your post.

      I didn't get the concept in here. Can you help me on a real example which is I am trying to do?

      I have USERS table and I want to make registration on that table. If the user is exists it will show me an error message. If not exits it will show me a success message.

      Assume that the table is only including userid,username and password.

      How can I do such an example?

      Thanks.

      Author's profile photo Ruben Carreño Pulido
      Ruben Carreño Pulido

      HI:

      I would like to do the same Stored Procured in HANA as Oracle. Saving in a table, error code, error msg and trace, is it posible?

      Here below is my SP

       

      3 hours ago|10 Views

      Hi:

      I have this error catch procedure in Oracle using sys.DBMS_UTILITY.

      Here is the Stored Procedure:
      CREATE PROCEDURE ENTERPRISE.Record_Error (
      IN errorcode VARCHAR(30 ),
      IN errormessage VARCHAR(4000)
      )

      LANGUAGE SQLSCRIPT AS
      l_code INTEGER ;
      l_mesg VARCHAR(32767) ;

      BEGIN
      if (errorcode IS NULL) then
      l_code := SQL_ERROR_CODE;
      else
      l_code := errorcode ;
      end IF ;
      if (errorcode is NULL) then
      l_mesg := SQL_ERROR_MESSAGE;
      else
      l_mesg := errormessage;
      end IF ;

      INSERT INTO error_log (error_code
      , error_message
      , backtrace
      , callstack
      , created_on
      , created_by)
      VALUES (l_code
      , l_mesg
      , sys.DBMS_UTILITY.format_error_backtrace
      , sys.DBMS_UTILITY.format_call_stack
      , CURRENT_DATE
      , USER);
      END;

      Thank you

      Author's profile photo Marcos Carvalho
      Marcos Carvalho

      Hi Ruben,

       

      I am trying to do the same as you. Did you find a solution?

       

      Regards.