running sql agent job step based on value returned by a query

I am creating a sql agent job in MS SQL server 2008 R2.
First step runs a query to get validation status from the database. I want to run the second step only if validation_status = 1, or quit the job returning failure if validation_status <>1
One way of accomplishing this is to create a new job with the second step and use an if statement in step1 to run the second job.
Is there a better way where I can I accomplish this in the same job, to run the second step based on the outcome of query on first step?

Thank you for your help.
patd1Asked:
Who is Participating?
 
patd1Connect With a Mentor Author Commented:
I used @raise_error and raised error if validation_status <> 1

This works for me.

Thank you for your help.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can set the value of  "Process Exit code of a successful command " for the fist step, by default it is zero.
You may need to alter the step to return a different value in case it is not successful using a return statement.
0
 
patd1Author Commented:
Aneesh, thanks for answering. Can you please give me an example for how to set Process Exit Code. Google didnt help much.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Goto job properties -> double click on step1 of the your job, just under the "Run As " selection box, you can see Process Exit Code text box
0
 
patd1Author Commented:
setting @raise error worked.
0
All Courses

From novice to tech pro — start learning today.