In SQL Agent you create a Job / Task, and then "attach" a schedule to it. Thus a Schedule can be reused for multiple jobs and is independent of the Job itself. A Schedule can be set up to run on specific days of the week, repeat at intervals, stop after a given date - or only run once - and so on. All sorts of very flexible features.
In our own Task Scheduler we didn't need lots of flexibility, so we just allow for some very specific schedules - Ten minutes, Hourly or Daily.
Why not use SQL Agent's scheduler? You could create one job for each SProc - that would save you having to create a scheduler of your own.
If you want to build your own scheduler I reckon you need to do the following:
-
Run your "Report Launch Sproc" periodically - you can do that using SQL Agent's scheduler, and launch it at the minimum granularity that you need (from your description that appears to be "weekly")
-
Find anything in your Reports table that is overdue, and execute it. Also, set its Next Run Time to the next anniversary date/time (this needs some care if a task is being run "late" as the intention is probably that it should NEXT be run on the anniversary of the original date, not that of the actual run-date. Issues such as adding one month to 31-January depending on whether it is a leap year or not, and so on. You need to handle the situation where a Report raises an error (you don't want your whole run to abort, the remainder of the reports still need to run
) . You also need to consider if the reports should run concurrently, or sequentially. The SQL Agent scheduler allows things to run either sequentially (a single Job with multiple Steps) or Concurrently (each task as a separate Job and then all scheduled at the same start time)