asked
which is the best way of approach
we do have 25 different store procedures for different processes, all needs to be run everyday midnight 12 PM one after other or at the same time ...Presently i am planning, keep all the store procedures in one main store procedure and keep that store procedure in a job and schedule the job. or better to create a 25 jobs and schedule them one after another. My one confusion is...suppose if any of the store procedure has a problem...will the other procedures will run? I tried the first approach..by keeping all the store procedures in one main store procedure ...in this approach..suppose if any of the store procedure is having problem...but still remaining store procedures are executing. Please guide me...which is the best way of approach...
You are not clear if you want all to run at the same time, or have some sort of dependency. If you want to stop the subsequent procedures from executing if one fails, then you can use a Job, with 25 steps - set the failure action on each job to be 'quit the job'. You can do this in one stored procedure, but depending on the nature of the error raised by a failing proc, you could use [SET XACT ABORT ON][1]. Another alternative in one stored proc would be [TRY..CATCH][2] blocks around each proc [1]:
http://msdn.microsoft.com/en-us/library/ms188792.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms179296.aspx
My suggestion, based on the way I understand your question, would be to create a SQL Agent job to run at the required time and create a step in the job for each procedure you want executed. You can set preferences on how to handle failed executions and have emails to operators to take action if necessary. It will also be easy to alter the order in which execution takes place and to add new procedures or remove others.
