Execute a series of SQL Server 2000 Stored Procedures
Posted on 2008-10-13
I have several stored procedures that I need to run in a weekly basis. What I need is to create a 'master' stored procedure that will have a loop in which the code:
- Gets the name of the stored procedure
- Executes it
- Makes sure it was successful
- Runs the next one.
And here's the fine print.
I will execute the 'master' stored procedure from a VB.NET Windows application.
The Stored Procedures update records based on criteria specified in said SPs.
I have a table where I have the names of these SPs, so I can select them from it as I loop.
Also, if one of the stored procedures FAILS for any reason, I need to rollback ALL the transactions done by the previous stored procedures from that list and spit out a message with the name of the offending stored procedure.
Does anyone have a good example of how to accomplish this?