Passing on SQLSTATE using SIGNAL to caller SP using
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?