How to make a SQL Server agent job step failure on purpose

Question

I have a process and the first step is to check status of other process. If the other process is done I would want to run the rest steps and if not I would want to quit the job.

I have a table that looks into the status and if all done it will mark as 'done'. So what should I put in the first step so that when the status is not 'done', it makes step 1 fail?

so you want SQL that always gives an error? such as 'select (Select 1 union SELECT 2) X' — Cato, Nov 11 '16 at 15:41

score 6 · Answer 1 · answered Nov 11 '16 at 16:27

There are lots of ways you can force sql-server to throw (or raise) an error but I would recommend using THROW for SQL-Server 2012 + or RAISERROR below that as that is your actual intention. So you could do something like:

IF EXISTS(SELECT * FROM StatusTable WHERE status <> 'done')
BEGIN
    ;THROW 51000, 'Process Is Not Finished', 1
END

score 1 · Answer 2 · answered Jul 17 '19 at 09:32

1

You can use also "Select 1/0" this will parse but it will giving error while execution

answered Jul 17 '19 at 09:32

Sunil Patil

829
8
14

That is what try/catch is for. Using 1/0 is a bad practice. – Sam Mar 19 '21 at 18:32

RichardBSmith · Answer 3 · 2023-01-02T01:22:53.020

0

I feel it is bad practice to fail a job-step to implement application logic. Better to use IF (some condition) THEN (some job-step action) ELSE (print an explanation why there was no job-step action). And reserve failures for operational issues.

edited Jan 02 '23 at 01:22

answered Jan 02 '23 at 01:17

RichardBSmith

11
1
3

How to make a SQL Server agent job step failure on purpose

3 Answers3