Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for each schedule type.

Is this possible?

share|improve this question
    
Can you give more details, e.g. are the schedules all on the top of the hour, do any of the procedures to run during more than one of the time slots, etc. –  Aaron Bertrand Jan 28 at 19:35
    
Sure. Basically I have a number of jobs that run according to various schedules and would like to consolidate these jobs into one. The schedules of the aforementioned jobs may run during more than one of the timeslots. Also, I think the schedules may all be at the top of the hour--is the there maybe a more robust solution? But anyway, hope this helps! –  Drew Jan 28 at 19:39
add comment

1 Answer

up vote 3 down vote accepted

If the jobs are all scheduled to run at the top of the hour, then you could simply have a single job, with a single step, and schedule the job to run every hour (or on select hours). Then the step can have logic like this:

DECLARE @h INT = DATEPART(HOUR, CURRENT_TIMESTAMP);

IF @h = 1
BEGIN
  EXEC dbo.ProcedureToRunAt1AM;
END

IF @h = 2
BEGIN
  EXEC dbo.OneProcedureToRunAt2AM;
  EXEC dbo.AnotherProcedureToRunAt2AM;
END

IF @h IN (4,16)
BEGIN
  EXEC dbo.OneProcedureToRunAt4AMand4PM;
END

If you need it to be more granular, you can check the minute, minute/10, etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.