All Forums Database
vikas.jain 4 posts Joined 05/15
31 May 2015
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:
dnoeth 4628 posts Joined 11/04
01 Jun 2015

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

Dieter

vikas.jain 4 posts Joined 05/15
01 Jun 2015

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?

Rohan_Sawant 55 posts Joined 07/14
01 Jun 2015

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

vikas.jain 4 posts Joined 05/15
01 Jun 2015

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
 

Rohan_Sawant 55 posts Joined 07/14
03 Jun 2015

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
 
 
 

maheshkanni 9 posts Joined 02/12
08 Jun 2015

Hi,
 
I am new to teradata stored procedures, I want to write a for loop or loop in SP.
 SET Count = 0;
SET S
 

maheshkanni 9 posts Joined 02/12
08 Jun 2015

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

You must sign in to leave a comment.