0

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.

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.

  • How would I check the result of Step 1 in Step 2? – Joe Schmoe Feb 27 '18 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 Feb 27 '18 at 15:50
  • Runs a SELECT query at the end of which I can do RETURN @RetCode – Joe Schmoe Feb 27 '18 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 Feb 27 '18 at 16:22
  • I'll just go with SSIS package – Joe Schmoe Feb 27 '18 at 18:14

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.