1

Is it possible to have an Agent Job with an input parameter?

What I'm looking to do is have a flag to determine how the job flow will occur. For example: I have a 10 step job. Steps 1-5 are always going to be executed. However, if the flag is set to 1, then then next step is going to be 6, and if the flag is not set, then execute step 8.

Can anyone please suggest how I could go about doing this? Thanks.

4
  • 1
    Your best bet for this would be to put a table in a database somewhere, and have each step execute a check on that table to see whether or not it should be executed. Using an IF (select val from tbl where val = step) = valYouExpect DOwork as an example. – Nic Mar 29 '17 at 19:49
  • Thanks for the input! If I understood this right, then the user would have to insert the data into the table each time the job has to be run? I should perhaps have mentioned that this would be a manually triggered job, based on the user's discretion. The usage I'm picturing for this is more along the lines of something like overwrite existing data, or store in separate table for comparison, which would be decided upon by the user. Having to enter data into a table just for this seems excessive work, and separate jobs seems shabby. Sorry for the lack of details in the question. – Anakimi Mar 29 '17 at 20:08
  • Actually, I will use your suggestion. Thank you! – Anakimi Mar 29 '17 at 20:21
  • I would say don't use Agent for this, that's not what it was made for. Use an actual enterprise scheduling engine, SSIS package, etc., Obviously you can make it work... but the right tool for the job is another story. – Sean Gallardy - Retired User Mar 29 '17 at 20:59
1

Expanding a bit on what @Nic said in his comment. Create a quick table in an applicable database.

CREATE TABLE JobParam AS (Param1 varchar(50))

Then set it appropriately before you start your job.

UPDATE JobParam SET Param1 = 'JobPath1'

As Nic suggested I would put some code at the top of steps 6+ like such.

IF (SELECT 1 FROM JobParam WHERE Param1 = 'JobPath1')
BEGIN
    T-SQL statements
END

Another option is to put code in the job that causes a failure if the param has a specific value and use the fail/succeed logic of the job steps to branch your job.

Either way I would make sure to put in a step at the end that always runs and re-sets your value to a default in case the job is run but someone forgot to update the parameter. Maybe have the default bypass the entire job.

UPDATE JobParam SET Param1 = 'FailJob'
1
  • Thanks @Kenneth, it seems like this is probably my best bet. Also, thanks @Nic! – Anakimi Mar 29 '17 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.