Can I catch an OUT variable(s) from a stored procedure?

Database
Enthusiast

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

Regards
stami27
3 REPLIES
Teradata Employee

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.

Enthusiast

Re: Can I catch an OUT variable(s) from a stored procedure?

One way is by handling errors inside the SP, and inserting into a table the error code and message from the "DECLARE HANDLER" block.

You can then query that table from bteq and check for the activitycount variable.
Enthusiast

Re: Can I catch an OUT variable(s) from a stored procedure?

Thanks for these suggetion AKIRA

greetings
stami27