Error anatomy and uncatchable errors

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

Error anatomy

INSERT INTO products (product_name, stock, price)
    VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'unique_name'. 
Cannot insert duplicate key in object 'dbo.products'. 
The duplicate key value is (Trek Powerfly 5 - 2018).

Error anatomy

Error number

An error message that highlights the text "Msg 2627", corresponding to the error number.

SQL errors: 1 to 49999
Own errors: starting from 50001

    select * from sys.messages

| message_id | language_id | severity | ... | text                                                            |
|------------|-------------|----------|-----|-----------------------------------------------------------------|
| ...        | ...         | ...      | ... | ...                                                             |
| 2627       | 1033        | 14       | ... | Violation of %ls constraint '%.*ls'. Cannot insert duplicate... |
| ...        | ...         | ...      | ... | ...                                                             |

Error anatomy

Severity level

An error message that highlights the text "Level 14", corresponding to the severity level of the error.

0-10: informational messages
11-16: errors that can be corrected by the user (constraint violation, etc.)
17-24: other errors (software problems, fatal errors)

Error anatomy

State

An error message that highlights the text "State 1", corresponding to the state of the error.

1: if SQL Server displays error
0-255: own errors

Error anatomy

Line

An error message that highlights the text "Line 1", corresponding to the line where the error occurred.

Procedure

An error message that highlights the text "Procedure Insert_product", corresponding to the name of the stored procedure where the error occurred.

Uncatchable errors

Severity lower than 11 (11-19 are catchable)
Severity of 20 or higher that stop the connection
Compilation errors: objects and columns that don't exist

Uncatchable errors - Compilation error example

BEGIN TRY
    SELECT non_existent_column FROM products;
END TRY
BEGIN CATCH
    SELECT 'You are in the CATCH block' AS message;
END CATCH

Msg 207, Level 16, State 1, Line 2
Invalid column name 'non_existent_column'.

Let's practice!

Transactions and Error Handling in SQL Server