Giving information about errors
Transactions and Error Handling in SQL Server
Miriam Antona
Software Engineer
Getting an error - review
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).
Getting an error - review
BEGIN TRY
INSERT INTO products (product_name, stock, price)
VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
SELECT 'An error occurred! You are in the CATCH block' AS message;
END CATCH
| message |
|-----------------------------------------------|
| An error occurred! You are in the CATCH block |
Error functions
ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the error severity (11-19).
ERROR_STATE() returns the state of the error.
ERROR_LINE() returns the number of the line of the error.
ERROR_PROCEDURE() returns the name of stored procedure/trigger. NULL if there is not stored procedure/trigger.
ERROR_MESSAGE() returns the text of the error message.
Error functions - examples
BEGIN TRY
INSERT INTO products (product_name, stock, price)
VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS Error_number,
ERROR_SEVERITY() AS Error_severity,
ERROR_STATE() AS Error_state,
ERROR_PROCEDURE() AS Error_procedure,
ERROR_LINE() AS Error_line,
ERROR_MESSAGE() AS Error_message;
END CATCH
| Error_number| Error_severity| Error_state| Error_procedure| Error_line| Error_message |
|-------------|---------------|------------|----------------|-----------|-----------------------------------------------------|
| 2627 | 14 | 1 | NULL | 2 | Violation of UNIQUE KEY constraint 'unique_name'... |
Error functions - examples
Last output
| Error_number| Error_severity| Error_state| Error_procedure| Error_line| Error_message |
|-------------|---------------|------------|----------------|-----------|-----------------------------------------------------|
| 2627 | 14 | 1 | NULL | 2 | Violation of UNIQUE KEY constraint 'unique_name'... |
Original error information
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 functions - examples
SELECT ERROR_NUMBER() AS Error_number,
ERROR_SEVERITY() AS Error_severity,
ERROR_STATE() AS Error_state,
ERROR_PROCEDURE() AS Error_procedure,
ERROR_LINE() AS Error_line,
ERROR_MESSAGE() AS Error_message;
| Error_number | Error_severity | Error_state | Error_procedure | Error_line | Error_message |
|--------------|----------------|-------------|-----------------|------------|---------------|
| NULL | NULL | NULL | NULL | NULL | NULL |
Error functions in nested TRY...CATCH constructs
BEGIN TRY
INSERT INTO products (product_name, stock, price)
VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
END TRY
BEGIN CATCH
BEGIN TRY
INSERT INTO myErrors
VALUES ('ERROR!')
END TRY
BEGIN CATCH
SELECT 'Outer CATCH block' AS 'Error_from',
ERROR_NUMBER() AS Error_number,
ERROR_MESSAGE() AS Error_message;
END CATCH
END CATCH
| Error_from | Error_number | Error_message |
|-------------------|--------------|-------------------------------------------|
| Outer CATCH block | 8152 | String or binary data would be truncated. |
Let's practice!
Transactions and Error Handling in SQL Server
