Stored Procedure Return Value to Fail SQL Job

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.

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.

In my opinion the best practice approaches are:

1. a function that returns a bit 1/0 (True/False)
2. stored proc output parameter

Proc return values are meant to be used to test the success or failure of the proc itself, usually 0 for success and some other error number when there is an error