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

sql-server sql-server-job
Jack picture Jack · Nov 11, 2016 · Viewed 12.9k times · Source

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?

Answer

Matt picture Matt · Nov 11, 2016

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

Related questions

How to start SQL Server job from a stored procedure?

How can I create a stored procedure to start a SQL Server job?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More