All Forums Database
WAQ 158 posts Joined 02/10
03 Dec 2015
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?

You must sign in to leave a comment.