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 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?
It seems like you have added a CONTINUE or EXIT handler in the procedure.
Just remove it and you will get your error.
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?
Can you please share the structure of code, not the exact code. Something like.
Replace xxx(in a,in b,out c)
Will then able to help you in a better way.
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 SQLTXT1='DELETE FROM '||DBNAME||'.TABLE';
SET SQLTXT2='INSERT INTO '||DBNAME||'.TABLE SELECT * FROM '||DBNAME||.TABLE1''
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 ERRMSG = MESSAGE_TEXT;
IF COUNT=2 OR SQL_ERR_CDE<>0
END LOOP L1;
Required quick response.