How to Handle Errors in SQL Server
In my previous blog, I talked about how I wanted to develop certain aspects of my SQL skills. One such aspect that I wanted to learn more about was how to handle errors when performing SQL scripts. There may be instances upon running a SQL script that it may not execute properly and it is important to understand why this occur and how to debug this.
In this blog, I will talk about error handling in SQL using information from DataCamp’s “Transactions and Error Handling in SQL Server” course. The dataset used in this course is based on an electric bike store, containing information about Products, Buyers, Staff, and Orders.
Error Anatomy
Before talking about how to handle errors, I should first talk about what an error is. An error occurs when the SQL script could not be completely executed for a certain reason. This reason can be anything from a table not existing to there being an issue with the software.
Within the first line of an error message, we have valuable information as to the details of the error.
Msg #, Level #, State #, Line #The first value corresponds to the error number from 1 to 49999. User-created errors start at 50001 but I will talk more about those later in the blog. A full list of every error and its details can be found here.
The second value is the severity level of the error. Level 0–10 are informational messages that serve more as warnings. Level 11–16 corresponds to errors that can be fixed by the user such as syntax errors or an object not existing. Levels 17–24 are the most severe, with them corresponding to issues like lack of resources in the software or an issue with the operating system.
The third value is the state of the error. The fourth value indicates on which line of your code that the error occurred, useful for debugging code when it goes awry.
Within SQL Server, there are prebuilt functions that are able to return details from the error message. These functions are named based on what feature they will return from the error message. ERROR_NUMBER() returns the error number, ERROR_SEVERITY() returns the error severity, and so on. These functions are commonly used within a TRY…CATCH statement.
TRY…CATCH
Suppose that in our Products table, we have a column called product_name in which we put a unique constraint on. Now, let’s say that I try to insert into Products a new item with a product_name that already exists within the table. I will receive an error that states :
Violation of UNIQUE KEY constraint 'unique_product_name'. Cannot insert duplicate key in object 'dbo.Products'.A way of preventing this error from occurring is to use a TRY…CATCH statement. This type of statement consists of two blocks of code in which we will TRY a segment of code and if an error arises from this, control is passed to the CATCH block. A TRY block starts with BEGIN TRY and ends with END TRY. The same syntax applies for CATCH, with BEGIN CATCH and END CATCH, denoting the start and end of the CATCH block respectively. If no error occurs after performing the code within the TRY block, the CATCH block will be skipped entirely.
BEGIN TRY
{sql_statement | statement_block}
END TRY
BEGIN CATCH
[{sql_statement | statement_block}]
END CATCHIt is also possible to nest TRY…CATCH statements within each other. Within a CATCH block, we can have another TRY and CATCH block. In the following example, we are attempting to enter a new buyer into the buyers table. If there are errors upon trying to inserting the buyer, we want to insert a new entry into a table called errors. If that entry into errors fails, then we will print a statement acknowledging failing to insert into errors.
-- Set up the first TRY block
BEGIN TRY
INSERT INTO buyers (first_name, last_name, email, phone)
VALUES ('Peter', 'Thompson', 'peterthomson@mail.com', '555000100');
END TRY-- Set up the first CATCH block
BEGIN CATCH
SELECT 'An error occurred inserting the buyer! You are in the first CATCH block';-- Set up the nested TRY block
BEGIN TRY
INSERT INTO errors
VALUES ('Error inserting a buyer');
SELECT 'Error inserted correctly!';
END TRY-- Set up the nested CATCH block
BEGIN CATCH
SELECT 'An error occurred inserting the error! You are in the nested CATCH block';
END CATCH
END CATCH
While this statement can be useful, there are certain errors that a TRY…CATCH statement will not be able to catch. Errors with a severity lower than 11 are not catchable because they are simply warnings. In addition, errors with a severity above 20 aren’t caught if they stop the connection. A TRY…CATCH statement will also not work if we have compilation errors such as executing a script on a table or column that does not exist.
Raising and Throwing Errors
We also have the ability to raise errors of our own. SQL Server provides two functions to raise errors: RAISERROR() and THROW().
-- Syntax for SQL Server and Azure SQL Database. Taken from link
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]Within the RAISERROR() function, we have multiple parameters for the message ID, the severity and state of the error, as well as additional arguments such as strings or numbers.
In the following example, we are attempting to select a product of a given product_id from the products table. If the SELECT statement does not find the product, it will raise an error stating that “No product with id 5” was found, with a severity level of 11 and a state of 1. Note that in RAISERROR(), we are allowed to use parameter placeholder such as %s and %d unlike THROW.
-- RAISERROR example
DECLARE @product_id INT = 5;IF NOT EXISTS (SELECT * FROM products WHERE product_id = @product_id)
RAISERROR('No product with id %d.', 11, 1, @product_id);
ELSE
SELECT * FROM products WHERE product_id = @product_id;
For a THROW statement, the parameters we pass in are the error number, error message, and error state. If we do not specify parameters for our THROW statement, it will re-throw the original error statement. One key note about the syntax is that THROW does not allow you to specify the severity of the error, setting it to 16 for all.
-- Throw statement syntax. Taken from link
THROW [ { error_number },
{ message },
{ state } ]
[ ; ]Another key note about THROW when using it within CATCH statements is that order matters. If we place our THROW before any lines of code within the CATCH statement, it will re-throw the original error, ignoring the lines of code after it within the CATCH statement. If it is placed after, however, any code preceding it will run and then it will THROW the error.
In the following example, we are trying to SELECT all information about a member from staff with a particular staff_id. If the statement does not find a member with that corresponding staff_id, we THROW an error with error number 50001, error message stating ‘No staff member with such id’, and a state of 1.
DECLARE @staff_id INT = 4;IF NOT EXISTS (SELECT * FROM staff WHERE staff_id = @staff_id)
-- Invoke the THROW statement with parameters
THROW 50001, 'No staff member with such id', 1;
ELSE
SELECT * FROM staff WHERE staff_id = @staff_id
I previously mentioned that THROW does not allow for placeholder parameters when generating an error message. We are, however, able to generate customized error messages through other means. If we generate our custom error message and declare it as a variable, we can then pass that variable within the message parameter. We can achieve this by either utilizing CONCAT() or FORMATMESSAGE() to help generate our messages with parameters.
DECLARE @first_name NVARCHAR(20) = 'Pedro';-- Concat the message
DECLARE @my_message NVARCHAR(500) =
CONCAT('There is no staff member with ', @first_name, ' as the first name.');IF NOT EXISTS (SELECT * FROM staff WHERE first_name = @first_name)
-- Throw the error
THROW 50000, @my_message, 1;
Those are the ways I learned how to deal with handling errors in SQL Server from this course. In my next blog, I will look to cover more about how SQL transactions work and how to ensure concurrency with SQL scripts. Thank you for reading!
