Sql-server – the recommended method of storing statements used in timer jobs

jobssql server

I've have a function that pulls data from several tables, arranges it nicely and generates an email summary which is to be sent out on a weekly basis to interested parties.

I decided a function was the right way to go for this since I needed to pass in parameters (which ruled out a view) but I didn't need to update the data (which ruled out a stored procedure) but I'm uncertain on how to store the statements that are going to do a few calculations and then call this function.

I believe I have two options:

  • Store it directly in the command as the first (and only) step of the job:
    enter image description here

or

  • Store it in a Stored Procedure and then call that from the command:
    enter image description here

Of the two I prefer the second method, it looks neater and keeps the logic of the job out of the call to the job. However, there is no need to call this process from anywhere else so it doesn't feel right to keep it in a stored procedure.

Is there a documented or recommended way of organising such statements? Am I following it with either of the examples above? If not, what should I be doing?

Best Answer

I think any answer to this question will be 'mostly' opinion based.

When I think of 'best practices' and comparing options, I think:

  • Is one way more secure?
  • Is one way easier to maintain?
  • Is one way adhering to company policy better?
  • Is one way more efficient or perform better?

Sql Server Agent Job information (and encapsulated code) is stored in the MSDB system database.

It's often desirable to emulate a lot of processes in other 'non-production' environments and people frequently back up their production user database(s) and restore them to 'test' environments. Storing your code in a user database (as a SP or Function) allows for that functionality to more easily be restored to a non-production environments during a 'refresh'. Storing this functionality in a Sql Agent Job limits that flexibility because you either have to manually edit the non-production job or script out the production job and recreate the job in the non-production environment. This can lead to production and non-production code not matching because someone didn't script the changes in the agent job.

Ultimately, you have to decide what works best for your company

Related Question