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.
|
1 2 3 4 5 6 7 8 9 10 11 |
DROP TABLE IF EXISTS Orders; GO CREATE TABLE Orders (OrderId INT PRIMARY KEY IDENTITY(1, 1), OrderNumber VARCHAR(20), OrderDef VARCHAR(100) ); GO ALTER TABLE Orders ADD CONSTRAINT Check_Order UNIQUE(OrderNumber); |
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.
|
1 2 3 4 5 6 7 |
tSQLt.ExpectException [ [@ExpectedMessage= ] 'expected error message'] [, [@ExpectedSeverity= ] 'expected error severity'] [, [@ExpectedState= ] 'expected error state'] [, [@Message= ] 'supplemental fail message'] [, [@ExpectedMessagePattern= ] 'expected error message pattern'] [, [@ExpectedErrorNumber= ] 'expected error number'] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
EXECUTE tSQLt.NewTestClass 'TestOrder' GO CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint] AS EXECUTE tSQLt.FakeTable 'Orders' EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order' EXECUTE tsqlt.ExpectException @ExpectedMessagePattern ='%Violation of UNIQUE KEY constraint%Check_Order%' INSERT INTO Orders (OrderNumber) VALUES('NewOrder') INSERT INTO Orders (OrderNumber) VALUES('NewOrder') GO EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint]' |
Create a new test class
|
1 |
EXECUTE tSQLt.NewTestClass 'TestOrder' |
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
|
1 2 |
EXECUTE tSQLt.FakeTable 'Orders' EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order' |
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?
|
1 |
EXECUTE tsqlt.ExpectException @ExpectedMessagePattern ='%Violation of UNIQUE KEY constraint% %' |
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.
|
1 2 |
INSERT INTO Orders (OrderNumber) VALUES('NewOrder') INSERT INTO Orders (OrderNumber) VALUES('NewOrder') |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint] AS EXECUTE tSQLt.FakeTable 'Orders' EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order' EXECUTE tsqlt.ExpectException @expectedErrorNumber =2627 INSERT INTO Orders (OrderNumber) VALUES('NewOrder') INSERT INTO Orders (OrderNumber) VALUES('NewOrder') GO EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint]' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint try_catch] AS EXECUTE tSQLt.FakeTable 'Orders' EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order' EXECUTE tsqlt.ExpectException @expectedErrorNumber =2627 BEGIN TRY INSERT INTO Orders (OrderNumber) VALUES('NewOrder') INSERT INTO Orders (OrderNumber) VALUES('NewOrder') END TRY BEGIN CATCH PRINT ERROR_NUMBER(); END CATCH GO EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint try_catch]' |
As you can see in the above image, the unit test failed because the error does not occur due to Try-Catch block.
tSQLt.ExpectNoException
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.
|
1 2 3 4 5 6 7 8 9 |
CREATE OR ALTER PROCEDURE TestOrder.[test math divide Exception] AS BEGIN EXEC tSQLt.ExpectNoException; DECLARE @Res AS FLOAT SET @Res= 1/1 END; GO EXEC tSQLt.Run 'TestOrder.[test math divide Exception]' |
As you can see the unit test passed. On the other hand, we look at the following test, it failed.
|
1 2 3 4 5 6 7 8 9 |
CREATE OR ALTER PROCEDURE TestOrder.[test math divide exception error] AS BEGIN EXEC tSQLt.ExpectNoException; DECLARE @Res AS FLOAT SET @Res= 1/0 END; GO EXEC tSQLt.Run 'TestOrder.[test math divide exception error]' |
[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.
Conclusion
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
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023