How do I make a sql job step quit reporting failure

Question

Hi

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 1

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"

Answer 2

You can use Try Catch

Begin Try
   exec myprocedure
   @Result = @Result output
End Try

Begin Catch
   /*Do whatever you want here*/
End Catch