I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code and each sending out its own individual email. The jobs are all on a schedule, some execute every 2 hours, some every 15 minutes, some once a week.
What I want to accomplish:
- Turn each job into a stored procedure.
- Create a user control table so that users only receive relevant alerts via SQL mail.
- Have one SQL job that controls everything from a job control table.
Basically, I want to move the maintenance and scheduling of these alerts out of SQL Server Agent and put it into a control table. Can this be accomplished so that each stored procedure runs on its own schedule without the need for having numerous, unwieldy jobs?