How do I make a sql job step quit reporting failure

sql-server-2005 tsql sql-job

CruelIO · Feb 5, 2010 · Viewed 10.2k times · Source

I have a sql job step

like this

Declare 
@Result varchar(255)

exec myprocedure
@Result = @Result output

What I want to do:
if @Result = 'Error' then mark the job as failed, how can I achieve that?

Answer

Add this to the end of your script:

if @Result = 'Error'
    raiserror('The stored procedure returned an error',16,1)

And make sure that on the "Advanced" tab of the step properties, the "on failure action" is set to "Quit the job reporting failure"