SQL Server Tutorial : Error anatomy and uncatchable errors

---

In this lesson, you will study the anatomy of errors. You will also learn which errors can't be detected by a TRY...CATCH construct.

Do you remember what happened when we tried to insert a new product with a name which was already stored in the products table? We got this error.
The first line of this error has several data which will be analyzed in the next slides. The rest of the lines are the message text of the error.

The second value is the severity level, 14 in this example. Severity levels from 0 to 10 are informational messages. The errors with this level of severity are treated as warnings. Levels from 11 to 16 are errors that can be corrected by the user. For example, constraint violations, as we got in our error message. Levels from 17 to 24 are other kinds of errors, such as software problems or fatal errors. The complete list of severity levels is available through Microsoft.

The third value is the state. It can give you more information about the error. It will be 1 if SQL Server displays the error to the user. If you raise your own error, you can choose a value between 0 and 255.

The last value is the line. It indicates on which line the error occurred. Finally, if the error happened within a stored procedure or a trigger, you will receive this extra data, giving you the name of the stored procedure or the name of the trigger.

The TRY...CATCH construct we learned can't catch every kind of error, for example, errors with a severity level lower than 11. As you learned in the error anatomy slides, these errors are considered as warnings. Errors with severities from 11 to 19 are catchable. Errors with a severity of 20 or higher aren't caught either if they stop the connection. However, if the connection is not stopped the TRY...CATCH will catch the error. And finally, compilation errors. For example, if we write the name of a table that doesn't exist.

Let's see one example where an error can't be caught.
In this code, we are trying to select from the products table a column named non_existent_column. If we execute this block of code, we get an error because the products table doesn't have this column.
Notice the output doesn't show the text 'You are in the CATCH block'. As this is a compilation error, the CATCH block can't handle the error.

Let's have fun with errors!

#DataCamp #SQLServerTutorial #TransactionsandErrorHandlinginSQLServer