Esat Erkec
SQL unit testing - tSQLt framework tsqlt.ExpectException result image

SQL Unit Testing: Working with exceptions

May 21, 2019 by

With this article, we will complete our journey with SQL Unit Testing. But first, let’s remember the main theme of the SQL Unit Testing again briefly. With the help of the SQL Unit Testing, we can detect and find out the flaws and bugs in database projects. This reduces the possible loss of time and money and reputation during the use of the product. However, it should be kept in mind that no matter how many tests are carried out, there will still be some bugs in the project, but SQL unit tests provide a greater degree of confidence. The fact that there are no bugs or flaws in the application at that time does not mean that the application is perfect. We cannot test all combinations and probabilities of the database objects. That is why in real life, considering certain parameters (risk, priority, etc.) we must ensure that a “reasonably sufficient” test is done.

  • Note: I suggested that you can check the previous articles (see the TOC at the bottom) It will be helpful to learn more details about SQL unit testing concept and tSQLt framework. Particularly, SQL unit testing with the tSQLt framework for beginners article can be very helpful to beginners. In this SQL unit testing article, we will go through how to test the behavior of the exceptions in the SQL unit tests. We can use two different procedure to test the behavior of the exceptions in the SQL unit tests.

In some cases, the result of the unit tests might be changing due to exceptions which are returned. Normally, we expect an exception in the test and if the exception occurs, the test must be successful. This type of unit testing called negative testing because we are testing unexpected and extraordinary conditions. In the tSQLt framework we can make it with help of the tSQLt.ExpectException procedure.

On the other side, we don’t expect any error in the test and if an error occurs during SQL unit testing, we want that, the unit test has to be failed. In the tSQLt framework we can make it with help of the tSQLt.ExpectNoException procedure. Now, we are going to look at how we deal with these cases and let’s learn details and usage of these methods.

tSQLt.ExpectException overview

Suppose that, we have created a table and then we added a unique constraint to this table to provide the data integrity of the table. How can we ensure that, this unique constraint will work properly? The answer is very simple; we try to insert the same value twice and in the second insert process; it must return an error. We have found the answer, but how to apply this logic to the SQL unit test. In the following section, we will learn how to overcome this SQL Unit testing issue. At first, we will create an example table which includes a unique constraint.

As we mentioned, SQL unit testing tSQLt framework offers tSQLt.ExpectException method. tSQLt.ExpectException method catches the error after a row in which it is used, and it compares this error according to the entered parameters. If the occurred error matches the tSQLt.ExpectException given parameters, the SQL unit test will be passed, otherwise, the SQL unit test will be failed.

On the other hand, if the error does not occur then the SQL unit test will be failed. The SQL unit test must fulfill two requirements to be succeed.

  • An error must occur after the tSQLt.ExpectException line
  • Error detail must match the expected error

Let’s create a SQL unit test for the unique constraint scenario which we mentioned before.

tSQLt.ExpectException syntax and usage

tSQLt.ExpectExpection method offers various parameters for flexible usage and all parameters of this method are optional.

Now, we will create a SQL unit test which includes tSQLt.ExpectException method and then we can handle this query line by line with detailed explanations.

SQL unit testing - tSQLt framework  tsqlt.ExpectException  result image

Create a new test class

In this part of the query, we created a new test class. It creates a database schema which related to tSQLt framework.

Create a fake table and add a constraint

In this part, we create fake table of the Orders table so that we obtain an empty table and, this table does not possess any constraint. For this reason, we should add unique constraints through the tSQLt.ApplyConstraint method so that Check_Order constraint will be created on the fake copy of the Orders table during the SQL unit test.

How to use tSQLt.ExpectException in SQL Unit Test?

In this part of the query, we set the tSQLt.ExpectException parameters. In the below, we use the @ExpectedMessagePattern parameter. This parameter specifies that if the error message look like this pattern thus the expected and actual errors will be match. If we try to insert a duplicate value to the table which includes a unique constraint, an error messages occurs which looks like below:

Msg 2627, Level 14, State 1, Line 23
Violation of UNIQUE KEY constraint ‘Unique Constraint name‘. Cannot insert duplicate key in object ‘Table name’. The duplicate key value is (Value Name).

Moving from this error message we set the @ExpectedMessagePattern as “%Violation of UNIQUE KEY constraint%” so that tSQLt.ExpectException can match the actual error and expected errors in the SQL unit test. On the other hand, we will try to create an error through the following insert statement. It is obvious that the second insert statement will return a unique value duplication error.

At the same time, we can use a @ExpectedErrorNumber parameter instead of the @ExpectedMessagePattern. @ExpectedErrorNumber parameter takes exact number of the error as a value.

SQL unit testing - tSQLt framework  @expectedErrorNumber  usage in the tsqlt.ExpectException procedure

I want to suggest that in this type of the SQL unit tests do not use Try-Catch blocks because it will be prevented from catching the errors.

SQL unit testing - tSQLt framework  tsqlt.ExpectException error

As you can see in the above image, the unit test failed because the error does not occur due to Try-Catch block.


If we don’t expect an error in the SQL unit test, we can use tSQLt.ExpectNoException method to check this circumstance. Normally, If any error occurs in the SQL unit test, this method failed to the unit test. We can use only one tSQLt.ExpectNoException per test. Now we will make a little example.

SQL unit testing - tSQLt framework  ExpectNoException usage

As you can see the unit test passed. On the other hand, we look at the following test, it failed.

tSQLt framework  ExpectNoException test fail

[TestOrder].[test math divide exception error] failed: (Failure) Expected no error to be raised. Instead this error was encountered: Divide by zero error encountered.[16,1]{TestOrder.test math divide exception error,6}

At this point, I want to review the message of the unit test. As you can see the tSQLt framework unit test message is very clear and it said that you don t expected any error however an error encountered.


In this article, we learned details of the tSQLt.ExpectException and tSQLt.ExpectNoException procedures. With the help of these procedures, we can test behaviors of the exception in SQL unit testing. The tSQLt framework offers various procedures however we should know the ability and parameters of these methods. In this way, we can create the most effective and useful SQL unit tests. As for the last sentence in this SQL Unit testing series, the tSQLt framework is the best utility to create SQL unit tests.

Table of contents

SQL Unit Testing with SSDT (SQL Server Data Tools)
SQL unit testing with the tSQLt framework for beginners
SQL Unit testing with the tSQLt framework and SQL Server Database Project integration
SQL unit testing best practices
How to use fake tables in SQL unit testing?
How to use fake functions with SQL unit testing?
SQL Unit Testing Mocking Stored Procedures
SQL Unit Testing: Working with exceptions

Esat Erkec
Testing, Utilities

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec