Skip to Content

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 πŸ™‚

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
    1. Krishna Tangudu 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

      (0) 
  2. sreedhara sameer kumar ilapavuluri

    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

    (0) 
  3. Kemal MΓΌderrisoglu

    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.

    (0) 

Leave a Reply