Stored Procedure Graceful Abort

Database

Stored Procedure Graceful Abort

Hi ,

I have a stored prodcedure. How do I make sure that the exit is not graceful. I am able to capture the error code and message but the stored procedure succeeds. 

In short how do I Abort a stored procdure and push it to failure state. 

Thanks,

Tags (2)
7 REPLIES
Senior Apprentice

Re: Stored Procedure Graceful Abort

Use SIGNAL or RESIGNAL in the outermost level without defining a handler for it.

Re: Stored Procedure Graceful Abort

Thanks for the response @dnoeth  , even after giving Signal towards the end , at bottom of SQL assistant I get " Call Completed , One row retruned "

But the answerset gets the one row with relevant return code and Message.

How do I make sure the message is " Call was unsucessfull" , Is there any such option?

Enthusiast

Re: Stored Procedure Graceful Abort

Hi Vikas,

It seems like you have added a CONTINUE or EXIT handler in the procedure.

Just remove it and you will get your error.

Thanks,

Rohan Sawant

Re: Stored Procedure Graceful Abort

Hi Rohan,

Did remove the Exit handler. Based on if condition , we did an Abort. But still it was an graceful exit and call completed successfully 

Any other Pointer on how to handle this?

Thansk,

Vikas Jain

Enthusiast

Re: Stored Procedure Graceful Abort

Hi Vikas,

Can you please share the structure of code, not the exact code. Something like.

Replace xxx(in a,in b,out c)

begin

create 

insert

end;

Will then able to help you in a better way.

Thanks,

Rohan Sawant

Enthusiast

Re: Stored Procedure Graceful Abort

Hi,

I am new to teradata stored procedures, I want to write a for loop or loop in SP.

 SET Count = 0;

SET S

Enthusiast

Re: Stored Procedure Graceful Abort

Hi,

I am new to teradata SPs n trying to write a loop.

Below is the sample code, I want to assign SQLTXT1,SQLTXT2 to SQLTXTand run with SYSEXECSQL. so I can run any no. of queries in loop and want to leave loop if SQL_ERR_CODE <> 0 .

SET Count=0

SET SQLTXT1='DELETE FROM '||DBNAME||'.TABLE';

SET SQLTXT2='INSERT INTO '||DBNAME||'.TABLE SELECT * FROM '||DBNAME||.TABLE1''

L1: LOOP

Q1: BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SET COUNT=COUNT+1;

SET SQLTXT='SQLTXT:COUNT';

CALL DBC.SYSEXECSQL(:SQLTXT)

END Q1;

GET DIAGNOSTICS EXCEPTION 1 ERRMSG = MESSAGE_TEXT;

IF COUNT=2 OR SQL_ERR_CDE<>0

THEN

LEAVE L1;

END IF;

END LOOP L1;

Required quick response.

Best Regards,

Mahesh