Identifying Status of CALL to Stored Proc?

Database
Teradata Employee

Identifying Status of CALL to Stored Proc?

When creating Stored Procedures, we’d like to be able to ‘logically abnormally terminate’ the Proc. - i.e. we do a query to a table and if a ‘logical’ condition exists (not an error – e.g. a row exists that shouldn’t ) we’d would like to LEAVE the Stored Proc and be able to detect a condition I set in the Proc before exiting similar to setting the error level in a BTEQ script so the process executing it can then test that condition and take appropriate action. Is there a way to do this? We’ve looked at the documentation and can not find anything that might allow us to do this. We probably missed something – worth asking here.
4 REPLIES
Teradata Employee

Re: Identifying Status of CALL to Stored Proc?

Hi,

You can make use of stored-procedure labels to do this conditional exit with user-defined return-code.

Regards,

MAC
Teradata Employee

Re: Identifying Status of CALL to Stored Proc?

I know how to exit using labels - not a problem. What exactly is a 'user defined return code'? Do you mean an OUT parameter that you set with a value? Not a problem, but, how do I then test that from within a BTEQ script? i.e. how is that 'data' accessable in BTEQ - i.e like checking IF ERRORCODE . . . I don't believe these are accessable as a 'parameter value'. BUT - I could just be wrong! If so, PLEASE correct me as this would be a good solution.
Junior Contributor

Re: Identifying Status of CALL to Stored Proc?

Hi JK,
in TD13 there's SIGNAL and RESIGNAL to throw errors in SPs, but i didn't try that yet.

In the SP Orange Book there's an example using an External SP to throw an error.
But the error code returned to BTEQ is always the same, you just set SQLSTATE and error message.

Dieter
Teradata Employee

Re: Identifying Status of CALL to Stored Proc?

Thank you so much Dieter. So I guess the answer is, there is no way to do this until 'maybe' TD13 (we're on 6.2 at the moment but planning to go to 13. I owuld hat thought there owuld be some way to do this from SPL - oh well - fooled again;-)