Menu
We use RAISERROR in SQL Server. The syntax is RAISERROR('Some Message.', 16, 1).
What is the use of the parameter values 16 and 1 with RAISERROR() in my example? I searched the internet and found that these parameters are called Severity and State. The documentation tells us some esoteric meanings to these values, but doesn't give us good direction on how to use them or why.
What I want to know is what is meant by Severity and State? How should they typically be used?
787
When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run: Outside the scope of any TRY block. With a severity of 10 or lower in a TRY block.
Is an integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.
Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY... CATCH will handle the error.
The severity level are displayed in the table below. Messages with a severity level of 0 to 10 are informational messages and not actual errors. Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user.
Error State is there to pin point the location where error occured in your code. Say if you have a 1000 lines long stored procedure and you are raising errors in different places, Error state will help you to tell which error was actually raised.
Error Severity gives information about the type of error that occured,
upto Severity level 10 are informational messages.
11-16 are considered errors that can be fixed by the user.
17-19 are considered Non-Fatal errors in Sql Server Resources, Engine and other stuff .
20-25 are considered Fatal Error which causes sql server to shut down the process immediately.
87
According to the docs on RAISERROR:
Severity
Is the user-defined severity level associated with this message. When using
msg_idto raise a user-defined message created using sp_addmessage, the severity specified onRAISERRORoverrides the severity specified insp_addmessage.Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with
ALTER TRACEpermissions. For severity levels from 19 through 25, theWITH LOGoption is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.State
Is an integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
