All Forums Database
stami27-2406 22 posts Joined 02/06
23 May 2006
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 advanceRegardsstami27

j355ga 100 posts Joined 12/05
23 May 2006

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.


DGiabbai 47 posts Joined 07/04
24 May 2006

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.

stami27-2406 22 posts Joined 02/06
26 May 2006

Thanks for these suggetion AKIRAgreetingsstami27

You must sign in to leave a comment.