Using variable in Teradata SIGNAL

Database
WAQ
Enthusiast

Using variable in Teradata SIGNAL

Hi,

I am using Teradata SIGNAL to raise an exception with my own SQL code and message. My SIGNAL code for raising the exception is given below:

SIGNAL SQLSTATE VALUE 'U0927' SET MESSAGE_TEXT = vError_Msg;

I want to know is there any way I can use variables in place of hardcoded SQLSTATE, something like the below one?

SIGNAL SQLSTATE VALUE vError_Code SET MESSAGE_TEXT = vError_Msg;

If not, is there any other alternative to acheive the same?

Thanks and Regards

Tags (2)
19 REPLIES
Teradata Employee

Re: Using variable in Teradata SIGNAL

hey,

you should always declare something like this on a sp to handle generic sql errors and warning:

 DECLARE EXIT  /* OR CONTINUE?*/ HANDLER FOR SQLEXCEPTION
BEGIN
SET v_SQL_ERR_CD = SQLCODE;
SET v_SQL_STATE = SQLSTATE;

/* CASE v_SQL_STATE WHEN ... THEN.. set MESSAGE_TEXT =... */

/* set whatever you want here for custom error message */

/* after you can even insert this message on a log table */
END;

DECLARE EXIT /* OR CONTINUE */ HANDLER FOR SQLWARNING
BEGIN
SET v_SQL_ERR_CD = SQLCODE;
SET v_SQL_STATE = SQLSTATE;

/* CASE v_SQL_STATE WHEN ... THEN.. set MESSAGE_TEXT =... */

/* set whatever you want here for custom error message */

/* after you can even insert this message on a log table */

END;
WAQ
Enthusiast

Re: Using variable in Teradata SIGNAL

Hi MaximeV,

Yes we are using handler but my question is that is there any way we can use the variable in SIGNAL statement?

Teradata Employee

Re: Using variable in Teradata SIGNAL

Actually i don't know (although I don't think it's possible) . I was just giving an option for generating custom and reusable error messages.

WAQ
Enthusiast

Re: Using variable in Teradata SIGNAL

Okay then that means that we can not SIGNAL/RESIGNAL the SQLSTATE dynamically from the caller procedure and the only way is to hardcode the SQL code in SIGNAL/RESIGNAL?

Teradata Employee

Re: Using variable in Teradata SIGNAL

SIGNAL requires a literal value or a (static) declared condition name. But you certainly have the option to RESIGNAL the original condition outward (by not specifying a condition on the RESIGNAL).

WAQ
Enthusiast

Re: Using variable in Teradata SIGNAL

Hi Fred, thanks for your response.

Actually I have a scenario where I have a set of custom SQLSTATEs (around 50) with messages which I want to raise based on certain conditions in Teradata SPs. Now there are different levels of SPs e.g L1 SP calling L2 SP which is then calling the main SP. I want to raise that custom exception in main SP and want that custom SQLSTATE and messages to pass through till L1 SP which is the first caller and eventually the invoker which is BTEQ.

Since SIGNAL does not allow to use SP variables for SQLSTATE, I cannot hardcode all custom SQLSTATES. I am using RESIGNAL from he handlers of main, L2 and L1 SP but L2 SP which is calling main SP does not get the SQLSTATE of the custom exception raise from the main SP.

What do you suggest, could be the best way to achieve this, if it’s not possible using variables?

WAQ
Enthusiast

Re: Using variable in Teradata SIGNAL

Can someone please help me in resolving the issue.

Teradata Employee

Re: Using variable in Teradata SIGNAL

Can you help me understand the issue?

L2 calls MAIN.

Logic within MAIN issues SIGNAL SQLSTATE 'U0100' SET MESSAGE_TEXT='Some custom message';

Does MAIN have a condition handler that intercepts this SQLSTATE? If so, the handler can RESIGNAL; with no options, to simply pass it outward to L2. If not handled within MAIN, then it will be automatically passed.

WAQ
Enthusiast

Re: Using variable in Teradata SIGNAL

Hi Fred,

L1 is calling L2 and L2 is calling MAIIN. All these SPs have handlers.

Yes you are correct, I am calling SIGNAL from MAIN SP with my custom SQLSTATE and message. Now the handler in main performs some action and then issue RESIGNAL (just literally RESIGNAL without any other parameter) to let L2 know which exception is raised.

Now the problem is that the RESIGNAL in main SP hanlder is not passing the custom SQL state to L2. So L2 is not aware of the custom SQL state raised my main SP.

Same goes with L1, as the control passes on to L1 from L2.

Thanks and Regards.