can we call stored procedures using before/after routines

[harithay]

Hi all,

before i run the etl job i need to populate a table using 'begin store procedure' and after running the job i need to update the same table using 'update stored procedure'.

My question is
can we call stored procedures using before job/stage and after job/stage routines

or
do i need to develop seperate server jobs to call stored proedure before and after etl job.


any suggestions.

Thanks

[kcbland]

One method is to write a .bat script using the command line interface to your unnamed database. If Oracle, I suggest using sqplus, if SQL-Server, OSQL/ISQL is a good choice.

Use the script in the before/after transformer/job routine call of ExecDOS.

[harithay]

Thanks kcbland
I have one more question for you, does this subroutine returns a value if we pass input parameters to stored procedure.
I

One method is to write a .bat script using the command line interface to your unnamed database. If Oracle, I suggest using sqplus, if SQL-Server, OSQL/ISQL is a good choice.

Use the script in the before/after transformer/job routine call of ExecDOS.

,

[kcbland]

Whatever you build into the script can pass the appropriate error codes back to DS. Non-zero return codes from shell scripts can generate a fatal error if you like. It's up to you to write a robust script that error checks the stored procedure and sets the proper script return code.