No matter how hard we try to design and develop applications, errors will always occur. There are two general categories – syntax or logical errors can be either programmatic errors or consequences of incorrect database design. Otherwise, you might get an error because of the wrong user input.
T-SQL (the SQL Server programming language) allows handling both error types. You can debug the application and decide what you need to do to avoid bugs in the future.
Most applications require that you log errors, implement user-friendly error-reporting, and, when possible, handle errors and continue application execution.
Users handle errors on a statement level. It means when you run a batch of SQL commands, and the problem happens in the last statement, everything preceding that problem will get committed to the database as implicit transactions. This might not be what you desire.
Relational databases are optimized for batch statement execution. Thus, you need to execute a batch of statements as one unit and fail all of the statements if one statement fails. You can accomplish this by using transactions. This article will focus on both error handling and transactions, as these topics are strongly connected.
SQL error handling
To simulate exceptions, we need to produce them in a repeatable way. Let’s start with the simplest example – division by zero:
The output describes the thrown error – Divide by zero error encountered. But this error was not handled, logged, or customized to produce a user-friendly message.
Exception handling starts by putting statements you want to execute in BEGIN TRY…END TRY block.
SQL Server handles (catches) errors in the BEGIN CATCH…END CATCH block, where you can enter custom logic for error logging or processing.
The BEGIN CATCH statement has to follow immediately after the END TRY statement. The execution is then passed from the TRY block to the CATCH block on the first error occurrence.
Here you can decide how to handle the errors, whether you want to log the data about raised exceptions or create a user-friendly message.
SQL Server has built-in functions which might help you to extract error details:
- ERROR_NUMBER(): Returns the number of SQL errors.
- ERROR_SEVERITY(): Returns the severity level that indicates the type of problem encountered and its level. Levels 11 to 16 can be handled by the user.
- ERROR_STATE(): Returns the error state number and gives more details about the thrown exception. You use the error number to search the Microsoft knowledge base for specific error details.
- ERROR_PROCEDURE(): Returns the name of the procedure or trigger in which error was raised, or NULL if the error did not occur in the procedure or trigger.
- ERROR_LINE(): Returns the line number at which the error occurred. It could be the line number of procedures or triggers or the line number in the batch.
- ERROR_MESSAGE(): Returns the text of the error message.
The following example shows how to handle errors. The first example contains the Division by zero error, while the second statement is correct.
BEGIN TRY PRINT 1/0 SELECT 'Correct text' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERR_NO , ERROR_SEVERITY() AS ERR_SEV , ERROR_STATE() AS ERR_STATE , ERROR_LINE() AS ERR_LINE , ERROR_MESSAGE() AS ERR_MESSAGE END CATCH
If the second statement is executed without error handling (SELECT ‘Correct text’), it would succeed.
Since we implement the custom error handling in the TRY-CATCH block, the program execution is passed to the CATCH block after the error in the first statement, and the second statement was never executed.
This way, you can modify the text given to the user and control what happens if an error takes place better. For instance, we log errors to a log table for further analysis.
The business logic might determine that the insert of the first statement fails when the second statement fails, or that you might need to repeat changes of the first statement on the second statement failure. Using transactions lets you execute a batch of statements as one unit that either fails or succeeds.
The following example demonstrates the usage of transactions.
First, we create a table to test the stored data. Then we use two transactions inside the TRY-CATCH block to simulate the things happening if a part of the transaction fails.
We will use the CATCH statement with the XACT_STATE() statement. The XACT_STATE() function is used to check if the transaction still exists. In case the transaction rolls back automatically, the ROLLBACK TRANSACTION would produce a new exception.
Have a loot at the below code:
-- CREATE TABLE TEST_TRAN(VALS INT) BEGIN TRY BEGIN TRANSACTION INSERT INTO TEST_TRAN(VALS) VALUES(1); COMMIT TRANSACTION BEGIN TRANSACTION INSERT INTO TEST_TRAN(VALS) VALUES(2); INSERT INTO TEST_TRAN(VALS) VALUES('A'); INSERT INTO TEST_TRAN(VALS) VALUES(3); COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() > 0 ROLLBACK TRANSACTION SELECT ERROR_NUMBER() AS ERR_NO , ERROR_SEVERITY() AS ERR_SEV , ERROR_STATE() AS ERR_STATE , ERROR_LINE() AS ERR_LINE , ERROR_MESSAGE() AS ERR_MESSAGE END CATCH SELECT * FROM TEST_TRAN -- DROP TABLE TEST_TRAN
The image shows the values in the TEST_TRAN table and error messages:
As you see, only the first value was committed. In the second transaction, we had a type conversion error in the second row. Thus, the entire batch rolled back.
This way, you can control what data enters the database and how batches are processed.
Generating custom error message in SQL
Sometimes, we want to create custom error messages. Usually, they are meant for scenarios when we know that a problem might occur. We can produce our own custom messages saying that something wrong has happened without showing technical details. For that, we are using the THROW keyword.
BEGIN TRY IF ( SELECT COUNT(sys.all_objects) > 1 ) THROW ‘More than one object is ALL_OBJECTS system table’ END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERR_NO , ERROR_SEVERITY() AS ERR_SEV , ERROR_STATE() AS ERR_STATE , ERROR_LINE() AS ERR_LINE , ERROR_MESSAGE() AS ERR_MESSAGE END CATCH
Or, we would like to have a catalog of custom error messages for categorization and consistency of error monitoring and reporting. SQL Server allows us to predefine the error message code, severity, and state.
A stored procedure called “sys.sp_addmessage” is used to add custom error messages. We can use it to call the error message in multiple places.
We can call RAISERROR and send the message number as a parameter instead of hard-coding the same error details in multiple places in the code.
By executing the selected code from below, we are adding the custom error into SQL Server, raise it, and then use sys.sp_dropmessage to drop the specified user-defined error message:
exec sys.sp_addmessage @msgnum=55000, @severity = 11, @msgtext = 'My custom error message' GO RAISERROR(55000,11,1) GO exec sys.sp_dropmessage @msgnum=55000 GO
Also, we can view all messages in SQL Server by executing the query form below. Our custom error message is visible as the first item in the resultset:
SELECT * FROM master.dbo.sysmessages
Create a system to log errors
It is always useful to log errors for later debugging and processing. You can also put triggers on these logged tables and even set up an email account and get a bit creative in the way of notifying people when an error occurs.
To log errors, we create a table called DBError_Log, which can be used to store the log detail data:
CREATE TABLE DBError_Log ( DBError_Log_ID INT IDENTITY(1, 1) PRIMARY KEY, UserName VARCHAR(100), ErrorNumber INT, ErrorState INT, ErrorSeverity INT, ErrorLine INT, ErrorProcedure VARCHAR(MAX), ErrorMessage VARCHAR(MAX), ErrorDateTime DATETIME );
To simulate the logging mechanism, we are creating the GenError stored procedure that generates the Division by zero error and logs the error to the DBError_Log table:
CREATE PROCEDURE dbo.GenError AS BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH INSERT INTO dbo.DBError_Log VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE() ); END CATCH GO EXEC dbo.GenError SELECT * FROM dbo.DBError_Log
The DBError_Log table contains all the information we need to debug the error. Also, it provides additional information about the procedure that caused the error. Although this might seem like a trivial example, you can extend this table with additional fields or use it to fill it with custom-created exceptions.
If we want to maintain and debug applications, we at least want to report that something went wrong and also log it under the hood. When we have a production-level application used by millions of users, consistent and reportable error handling is the key to debug issues in runtime.
While we could log the original error to the database error log, users should see a more friendly message. Thus, it would be a good idea to implement custom error messages that are thrown to calling applications.
Whatever design you implement, you need to log and handle user and system exceptions. This task is not difficult with SQL Server, but you need to plan it from the beginning.
Adding the error handling operations on databases that are already running in production might involve serious code refactoring and hard-to-find performance problems.