What do the different RAISERROR severity levels mean?

Question

My best google result was this:

• below 11 are warnings, not errors
• 11-16 are available for use
• above 16 are system errors
• there is no behavioral difference among 11-16

But, from BOL, "Severity levels from 0 through 18 can be specified by any user."

In my particular stored procedure, I want the error returned to a .Net client application, so it looks like any severity level between 11-18 would do the trick. Does anyone have any authoritative information about what each of the levels mean, and how they should be used?

Answer 1

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.

Answer 2

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.