Force job to fail based on condition of IF

• dangoodwin96

  SSC Enthusiast

  Points: 174

  More actions

  April 1, 2020 at 3:40 pm

  #3739686

  I am using SQL Server 2005 and I need to force a job to fail based on a condition of an IF statement. This is an overview what my code is doing:

  IF (SELECT '1') = 1

  BEGIN

  PRINT 'Yes'

  END

  ELSE

  BEGIN

  PRINT 'No'

  END

  The above isn't my exact code but this gives a very short overview what my code is achieving at present.

  Basically where it says PRINT 'No' I want it to fail the job instead so it can send an email notification out based on that failure.

  Is that possible to accomplish?

  Thanks in advance.

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  April 1, 2020 at 6:14 pm

  #3739751

  You should be able to use RAISERROR with a severity of, say, 16.  Please see the following for more info on RAISERROR.

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)