Raise custom exceptions

The SQL exception handling is a very powerful feature.
Yet, you must use it carefully.

It's important to understand that the "Catch" blocks swallows exceptions and application's Data Access Layer gets no information regarding SQL errors during execution.
Therefore it can take allot of time to understand if there was error at all!

In cases when it's crucial to notify calling application about execution crushes you can use RAISERROR routine.

RAISERROR – able to generate system defended as well as custom errors.

BEGIN TRY
    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();
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;