How do I raise an error that SQL will see as a job failure?

I've got a job scheduled through the SQL Server Agent that runs a sproc which runs some other sprocs. Every sproc looks like this:

BEGIN TRY
    -- do stuff
END TRY
BEGIN CATCH
    DECLARE @errorMessage varchar(4000)
    DECLARE @procName varchar(255)
    SELECT @errorMessage = error_message()
    SELECT @procName = OBJECT_NAME(@@PROCID)

    RAISERROR('%s threw an exception: %s', 16, 1, @procName, @errorMessage)
END CATCH

This all works fine - errors are raised and thrown up the stack, life is good. However, my RAISERROR calls don't appear to cause the job to fail - I'm set to receive an e-mail notification "When the job fails," but never receive one开发者_Python百科. E-mail notifications are working, as I will get emails if I change the notification to "when the job succeeds". Is there some other function I should be using here in place of RAISERROR?

---

Raise an error in the try block with severity between 11-19 in TRY block and then re-raise the same error in catch block. This will make the step fail..

code snippet from msdn

BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to 
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
           16, -- Severity.
           1 -- State.
           );
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
           @ErrorSeverity, -- Severity.
           @ErrorState -- State.
           );
END CATCH;

---

Each job step has an action for step failure, they must set to fail the entire job. Yours is probably set to go to next step only.

---

If your error severity level is 20 or higher, the database connection will be terminated. This will cause the step to fail. So long as your step is set so that the job fails if the step fails, you'll get your desired outcome.

RAISERROR('%s threw an exception: %s', 20, 1, @procName, @errorMessage) WITH LOG;

SEE msdn description of RAISERROR: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql