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?

Narceine

I don't know for other versions but I was very surprised to see that with SQL Server 2008, severity 16 do NOT terminate execution. – Milomilon

I use the same trick for a Delphi application, after trial and error i use level = 13. In this way the execution is not stopped (i raise the error in a trigger to display info the user) and the user gets the message. Of course it is a "terrible bad practice" but effective. – Colburn

"Terrible bad practices" are necessary to get around many, many, many MS obstacles. Getting around MS obstacles, though, is usually a "good practice". – Palingenesis

For anyone interested in a more in-depth exploration of sqlserver error handling see Erland Sommarskog's excellent series sommarskog.se/error_handling/Part1.html – Ruffin

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.

Rothwell

The MSDN link kind of says it all -- the information was right there in BOL, and I've never seen it before. Thanks! – Narceine

When you say "default", do you mean that an error level of 16 will be used if I call RAISERROR, passing no parameters. i.e. it will be caught by a catch block? – Chain

Level 16 doesn't terminate execution. See #76846. Unless I've missed something, you may want to update your answer. – Foveola

A foreign key violation error also has severity 16. Other data integrity errors could also have this severity, I don't know, but if you're raising and handling "business rules" errors, 16 may be misleading. In my case, I ended up using out parameters for success (true or false) and error message. – Misunderstanding

@Foveola Is absolutely right, Level 16 does not terminate execution just tested on both SQL Server 2008 R2 and SQL Server 2012. Rolled back edit. – Sandysandye

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.

Acrolith

Does not terminate on Sql 2016 even with the @divideByZero – Fuzzy