Raising custom error message in HANA Stored procedure

[former_member201560]

Dear all,

I need to raise custom error message inside hana stored procedure. For example, if the customer id is input to the stored procedure, I will check in the customer table, if the customer id doesnt exist or inactive. I need to throw error message as "Customer is not active or doest not exist".

Please let me know how to achieve by raising custom error message. If you have any code snippet, that will be of great help.

Regards,

Yogesh

[lbreddemann · Accepted solution]

Hi Yogesh,

you already got the code snippets you are looking for on your computer.

Simply open the online help in SAP HANA studio (press F1) and search for "Exception Handling Examples".

That will take you directly (or after a one time indexing run if you never opened the online help before) to the example coding you requested.

- Lars

[former_member201560 · Accepted solution]

Hi Lars,

Thanks for your reply.

I already looked into the online help and SAP HANA SQL reference guide on General exception handling and Conditional exceptional handling. I tried those sample code along with SIGNAL and RESIGNAL commands, but it doesnt helped me in throwing my own custom error message.

It was just provided me the pre-defined error messages like "no data found" / "unique constraint violation"

For ex:

CREATE PROCEDURE MYPROC1 AS
BEGIN
    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
    DECLARE EXIT HANDLER FOR MYCOND RESIGNAL;
    INSERT INTO MYTAB VALUES (1);
    SIGNAL MYCOND SET MESSAGE_TEXT = 'my error';
    -- will not be reached
END;

CALL MYPROC1;

After executing this procedure, I got the below error instead of custom error message "my error"

[301]: unique constraint violated: [301] <myschema>.MYPROC1: line 5 col 5 (at pos 139): unique constraint violated exception: unique constraint violated: Table(MYTAB), Index(_SYS_TREE_RS_#9067469_#0_#P0)

It would be of great help if you can let me know what is the way to throw custom error message.

Regards,

Yogesh

[former_member182302 · Accepted solution]

Hi Yogesh,

As mentioned by you can use "Signal" in the exception handling block to raise the custom error message you are looking for..

you may want to have a look on this blog of mine where i explained on how to get the custom error messages.

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Ok, first of all: if you already made this experiment, you could have improved the chances to get a more fitting reply by telling that you did.

Other than that, you don't define an exit handler for the standard error code 301 and you don't signal your own error message then.

CREATE PROCEDURE MYPROC1 AS

BEGIN

    DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301;

    DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, 'my error' FROM DUMMY;

    INSERT INTO MYTAB VALUES (1);

    -- will not be reached

END;

CALL MYPROC1;

:1  'my error'

301 my error 

That does the trick here.

- Lars

[former_member201560 · Accepted solution]

Thank you Krishna. Your blog helped me a lot in raising the custom error message.