Passing on SQLSTATE using SIGNAL to caller SP using

Database
WAQ
Enthusiast

Passing on SQLSTATE using SIGNAL to caller SP using

Hi,

I have scenerio where I am raising a custom exception using SIGNAL statement in SP named TEST_DEPT. The SIGNAL statement is given below:

SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = '"This is an error message 2"';

I am calling TEST_DEPT SP from another SP called TEST_L1. The Body of TEST_L1 is given below:

REPLACE PROCEDURE TEST_L1(P_DEPT_NAME VARCHAR(30))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE vSQL_State VARCHAR(6);
RESIGNAL;
END;
CALL TEST_DEPT(927, P_DEPT_NAME);
END;

Now when I call TEST_L1, I get the custom message which is coming from SIGNAL statement in TEST_DEPT, however I am unable to get the SQLSTATE (which is U0123). Following is the output of error message:

CALL Failed 7693:  TEST_L1:"This is an error message 2"error message 2"

Can someone please tell me how to get the custom SQLSTATE with the message?

Tags (2)