Stored Procedure Return Value to Fail SQL Job

sql sql-server stored-procedures return-value sql-server-agent
Adrian S picture Adrian S · Sep 2, 2011 · Viewed 7.7k times · Source

I have a stored procedure which is the first step in an SQL Job. The SP compares dates in 2 tables, if they equal then the SQL job can continue, but if they are not I need to return value from the SP that causes the SQL Job to trigger it's on failure action.

What is the best way to do this? Is it with an RAISERROR statement or just return a value like -99?

Seems such an obvious question, but I've never thought about it before until now.

Answer

Derek Kromm picture Derek Kromm · Sep 2, 2011

Whichever way you want. You can use a try/catch and raiserror. That can allow you to write the error to the event log as well if desired.

Typically, I'd just use an IF statement that does a return and then proceed with the rest of the code otherwise.

I think it's a matter of preference/requirements, though. Either will get the job done.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More