Can I catch an OUT variable(s) from a stored procedure?
Hi stored procedure experts,
know someone if I can catch an OUT variable(s) from a stored procedure after calling it from a bteq on Unix Teradata V2R.05.01 or exist somthing like .IF ERRORCODE <> 0 THEN .QUIT ERROCODE or .IF ACTIVITYCOUNT = 0 THEN ....? I want to analyse in the BTEQ, if the Job of the stored procedure was OK done or no. Thanks in advance
Re: Can I catch an OUT variable(s) from a stored procedure?
Unfortunately error handling with SP in Teradata is none too clean.
You can display an OUT using SQL Assistant and in BTEQ.
You can also use an OUT from another SP called within the SP.
From SQL Assistant place a ? in place of the OUT parameter - as in: call mydb.my_sp(?);
From Bteq the ? in the above example would become the name of the OUT parm.
Also you could insert a message string to a temp table and then retrieve that value after the call completes.
If the only thing you are trying to do is to catch errors as opposed to displaying a value then just don't handle the exception in the SP. This will allow you to detect the error after the call.
Another way - if you do have error handling within the SP then after you handle the exception you could throw another artificial error, such as divide by 0. This will allow you to exit with some grace.