0

I have a series of stored procedures (27 in total) that I would like to execute sequentially in an automated way. I have created the required 27 job steps in SQL Server Agent (each step executing a stored procedure).

However, some of these procedures require a parameter to fill out called LastEndOFMonthDate where you fill out the date (ie 03/24/2018) in the parameter. This parameter would have the same value (date) for each job step. I was wondering how I could automate the parameter aspect, so that I would not have to manually enter the same parameter value for each stored procedure/job step that required it?

Here is an example of a stored procedure I am executing as one of the 27 job steps: 

exec [dbo].[sp_05_UpdateDataMart_26_Create_JMIDataMart_With_Demo_and_Total] @LastEndOfMonthDate

If anyone could provide an easy way to automate the parameter aspect of this, I would greatly appreciate it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sam
  • 47
  • 1
  • 7

1 Answers1

0

First, you don't need to create 27 steps to execute 27 procedures, you can put them all in 1 step and calculate your parameter 1 time as the first row of the step.

You can also write a wrapping procedure (main) that will call all your 27 sp using the parameter passed in main.

If it's necessary to be able to exit the whole job on errors in some procedures and to continue in case of others, instead of 27 steps you can make 1 SSIS package with 1 parameter at the package level, and assign the correct actions on success/complete/failure for every 27 ExecuteSQLTasks within a package.

The last idea (requires to rewrite your procedures) is to get the parameter from a table. Every proc should read the parameter as its first step from the same table. This way you can store the value DeltaDays (if I understood you correctly you want to execute your procs for a date "N days ago"), so you put this value in a table once, and all the procs read the value and calculate the correct date on their own, and you don't need to pass in anything, just schedule these procs.

sepupic
  • 8,409
  • 1
  • 9
  • 20