Implementing Error and Transactions Handling in SQL Server

Introduction

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:

SELECT 1/0

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.

Using transactions

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.

Conclusion

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.