2

Is conditional execution possible for SQL Server Agent job steps?

That is if Step 1 returns 1 I want to go to Step 2.

If Step 1 returns 2 I want to go to Step 3.

If Step 1 returns 3 I want to go to Step 4.

It looks like it is not possible because the only distinction available for step completion is Success or Failure but I figured I would check with the experts here.

I know I can achieve this logic in SSIS package but was wondering if it can be done within SQL Server Agent.

Improve this question

1 Answer 1

Reset to default
0

You can't configure the agent to act as you have described, but you might be able to achieve the desired result by building the logic into the code executed in steps 2, 3, and 4. For instance you could use an IF statement at the start of step 2 to check the result of step 1, and only continue if the result was 1. If the result is not 1, then it would move on to step 3, and so on.

Improve this answer
5
  • How would I check the result of Step 1 in Step 2?
    – Joe Schmoe
    Commented Feb 27, 2018 at 15:43
  • I probably should have asked for clarification first, since I don't know what your agent job is doing, so let's start there. What is step one doing in your job?
    – Padwan
    Commented Feb 27, 2018 at 15:50
  • Runs a SELECT query at the end of which I can do RETURN @RetCode
    – Joe Schmoe
    Commented Feb 27, 2018 at 15:55
  • 1
    In that case you would either have to log the results to a separate table, and query that table in your steps 2, 3, and 4. Or, is it possible for your step 1 to be built into steps 2, 3, and 4? Basically eliminate step 1 and step 2 would start by executing the code from step 1 and generating @RetCode, and would either continue or stop based on the code.
    – Padwan
    Commented Feb 27, 2018 at 16:22
  • I'll just go with SSIS package
    – Joe Schmoe
    Commented Feb 27, 2018 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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