Calling Procedures within the Procedure

Database
Enthusiast

Calling Procedures within the Procedure

Hi Guys,

Could any one can help me with the procedures the way that I am trying to execute?

I have 5 procedures which I need to call them one after the other(eventhough no Dependencies), So I have scheduled a Bteq Job in which we are calling one proc which it will be calling these 5 Procs.(this bteq is scheduled (Via Autosys)and run at a perticular timmings on every day).

each proc runs for 30-45 mins. Due to some reasons if the Last proc fails , my Support team will backout the success proc changes quick off the Scheduled job(which it will run all the  success procs as well).

As I have very minimual control on  Scheduled Bteq Job, I am trying to make changes to the Single proc which will call all the other procs.

For this purpose I am trying to create a Lookup table which will have the list of procs to be called and The run status Run time/date and run Flag.

If all the Procs run flag is "Y", will update them as "N" . If some of them are "N",just simply call those Procs which are flaged "N".

With this approach I can able to get the list of Procedures that failed (runflag as "N"),so that I can call those procs only.

But I am facing difficulty in calling the Procedure as CALL :Variable;

Is there any way that I can pass procedure name as a variable to call it?

I tried dbc.SysExecSQL, no luck.

Please let me know if anyone has better idea.