What do the different RAISERROR severity levels mean?

小开

Severity Level 16 can Terminate Execution.

TRY-CATCH Caveats with RAISERROR():

RAISERROR() with a Severity of 16 terminates execution for everything below the offending line.
However this only applies when inside a Try-Block.

--DECLARE @DivideByZero Int = 1/0--Uncommenting this will Skip everything below.
RAISERROR (N'Before Try: Raise-Error 16.', 16, 0)--Works.
SELECT 'Before Try: Select.'[Marker]--Works.
BEGIN TRY
RAISERROR (N'Inside Try: Raise-Error 16.', 16, 0)--Not displayed,but sends to Catch-Block.
SELECT 'Inside Try: Select.'[Marker]--Skipped.
END TRY
BEGIN CATCH
RAISERROR (N'Inside Catch: Raise-Error 16.', 16, 0)--Works.
SELECT 'Inside Catch: Select.'[Marker]--Works.
--RETURN --Adding Return will only skip what is After the Catch-Block for this scope only.
--;THROW--Shows the RAISERROR() from the Try-Block and Halts Execution. Must include ";".
END CATCH
RAISERROR (N'After Try-Catch: Raise-Error 16.', 16, 0)--Works.
SELECT 'After Try-Catch: Select.'[Marker]--Works.

Surprised? So was I.
What also threw me for a loop is not all Severity-16's are the same.
If you were to uncomment the Divide-By-Zero line at the very top, then nothing below it would run.
The Divide-By-Zero logic will also generate a Severity-16 Exception,
but it is handled with a full-stop, unlike when thrown with RAISERROR().

Note: Use ;THROW as the last line inside your Catch-Block to properly
throw the SQL Exception for the RAISERROR() event trigged by your Try-Block.
This will effectively halt execution with a full-stop.
The ; Semicolon is required when other lines exist in the Catch-Block before calling ;THROW.
If your logic properly handles the error in the Catch-Block (and you would like to continue processing
the rest of the logic after it), then do not use ;THROW.

Conclusion:

Do not conflate a Severity-16 thrown by the SQL-Server-Engine
with one you raise yourself using RAISERROR().
For all intents and purposes (when deliberately throwing your own errors), consider only 2 Severities:
0 (for Informational or Warning) and
16 (for Throwing an Exception handled within a Try-Block - to kick it out to the Catch-Block).

Information Now!

Note: If you are using RAISERROR() to display Informational Messages,
then I suggest using WITH NOWAIT:

RAISERROR('Read me right now!', 0, 1) WITH NOWAIT
RAISERROR('Read me whenever.' , 0, 1)
DECLARE @WaitSeconds Int = 10
DECLARE @WaitFor DateTime = DATEADD(SECOND, @WaitSeconds, 0)
WAITFOR DELAY @WaitFor

This is especially useful during long batch operations when you'd like some insight
into how things are progressing as you reach certain milestone markers throughout the batch.
By not using WITH NOWAIT, you may never know when your Informational Messages might appear.
They could appear intermittently throughout the course of the batch, or all at once when the batch completes.

最佳答案

Database Engine Severity Levels

You should return 16. Is the default, most used error level:

Indicates general errors that can be corrected by the user.

Don't return 17-18, those indicate more severe errors, like resource problems:

Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.

Also don't return 11-15 because those have a special meaning attached to each level (14 - security access, 15 - syntax error, 13 - deadlock etc).

Level 16 does not terminate execution.

When your intention is to log a warning but continue execution, use a severity level below 10 instead.