0

I have a SQL Server Agent job that has 10 steps. All of them need the same date as a parameter but I have to go through each step to update the parameter each time I run it (which is once per month). Is there a way to enter the date once and have it update on all of the steps in the job without having to create a table, write code, etc?

anonymous
  • 113
  • 13
  • No, each job is run in its own scope; they can't share variables. – Thom A Jan 25 '23 at 14:09
  • You could try updating the respective system table directly (https://stackoverflow.com/questions/298494/how-to-bulk-amend-the-job-step-command-in-all-sql-server-agent-jobs). – Roman Jan 25 '23 at 16:36

1 Answers1

0

I don't know what the parameter is, but maybe it would be a good idea to calculate it if this is possible.

Eg. you need the last month:

DECLARE @LastMonth int = MONTH(DATEADD(mm, -1, GETDATE()))

Or if the year needs to be included:

DECLARE @LastMonth nvarchar(7) = CAST(YEAR(DATEADD(mm, -1, GETDATE())) AS nvarchar(4)) + '-' + RIGHT('00' + TRIM(CAST(MONTH(DATEADD(mm, -1, GETDATE())) AS nvarchar(2))), 2)

Then you don't have to change it every month...

Michael Tobisch
  • 1,034
  • 6
  • 15