Unit testing: How to write your first unit test for T-SQL code

The object we want to test is a Trigger!

8 min readFeb 6, 2021

In the previous article, the second of this series, we described how to install the tSQLt framework, the Red-Gate SQL Test tool and how a unit test is runs. Now it’s time to write our first unit test! We will use the tools we described previously!

Requirements

We will write our first unit test to verify the requirements of a trigger requested by the fictitious company Adventure Works LTD whose database is available for download on GitHub at this link.

The AdventureWorks2017 database contains the Production.Product table that stores products managed and sold by the Company. The trigger is to prevent the insertion of new products with values less than 10 as a “safety stock”. The Company wishes to always have a warehouse stock of no less than 10 units for each product. The safety stock level is a very important value for the automatic procedures: it allows to re-order materials. The creation of new purchase orders and production orders are based on the safety stock level. To make our trigger simple, it will only respond to the OnInsert event, for INSERT commands.

To follow, you can find the Trigger’s code. The Production.usp_Raiserror_SafetyStockLevel stored procedure centralizes the error management.

USE [AdventureWorks2017];CREATE OR ALTER PROCEDURE Production.usp_Raiserror_SafetyStockLevel
(
  @Message NVARCHAR(256)
)
AS
BEGIN
  ROLLBACK;
  RAISERROR(@Message, 16, 1);
END;CREATE OR ALTER TRIGGER Production.TR_Product_SafetyStockLevel ON Production.Product
AFTER INSERT AS
BEGIN
  /* 
     Avoid to insert products with safety stock level lower than 10!
  */
  DECLARE @SafetyStockLevel SMALLINT;  SELECT
    @SafetyStockLevel = SafetyStockLevel
  FROM
    inserted;  IF (@SafetyStockLevel < 10)
    -- Error!!
    EXEC Production.usp_Raiserror_SafetyStockLevel
      @Message = 'Safety stock level cannot be lower than 10!';
END;
GO

Definition of the System Under Test

The first thing to do before starting to write a unit test is the definition of the System Under Test (SUT). The System Under Test have to be isolated from other objects so that it will not be affected by the behavior of other pieces of code, procedures or functions. In this example, the System Under Test is the trigger named TR_Product_SafetyStockLevel.

Definition of test cases

Once the System Under Test has been defined, before starting to write unit tests it is necessary to identify the test cases thinking about the requirements that the System Under Test must meet: Our trigger must prevent the insertions of new products with safety stock values below 10. The test cases to be transformed into unit tests are:

Insert new product with safety stock greater than or equal to 10
Insert new product with a safety stock less than 10
Multiple inserts of new products (all at once), the first one with a safety stock greater than 10
Multiple inserts of new products (all at once), the first one with a safety stock less than 10

Are you thinking about other test cases? Which ones? Do you think the fourth test is unnecessary because it is already included in the third one? We will see it shortly! Each single test must verify only one condition, so we will have to write four-unit tests, basically four stored procedures.

Our first unit test

All unit tests related to the object we want to test must be contained within a “test class” of the tSQLt framework. To create a new test class, we can simply invoke the stored procedure tSQLt.NewTestClass specifying the name of the class we want to create. The following T-SQL code creates the new test class named UnitTestTRProductSafetyStockLevel. The same operation can also be done with the graphical user interface of the Red-Gate SQL Test tool.

USE [AdventureWorks2017];-- Create new test class
-- Test class collects test cases
EXEC tSQLt.NewTestClass 'UnitTestTRProductSafetyStockLevel';
GO

It’s time to write our first unit test, here it is:

USE [AdventureWorks2017];
GOCREATE OR ALTER PROCEDURE UnitTestTRProductSafetyStockLevel.[test try to insert one wrong row]
AS
BEGIN
  /*
    Arrange:
    Spy the procedure Production.usp_Raiserror_SafetyStockLevel
  */
  EXEC tSQLt.SpyProcedure 'Production.usp_Raiserror_SafetyStockLevel';  /*
    Act:
    Try to insert one wrong rows with SafetyStockLevel lower than 10
  */
  INSERT INTO Production.Product
  (
    [Name]
    ,ProductNumber
    ,MakeFlag
    ,FinishedGoodsFlag
    ,SafetyStockLevel
    ,ReorderPoint
    ,StandardCost
    ,ListPrice
    ,DaysToManufacture
    ,SellStartDate
    ,rowguid
    ,ModifiedDate
  )
  VALUES
  (
    N'Carbon Bar 1'
    ,N'CB-0001'
    ,0
    ,0
    ,9 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  );  /*
    Assert
  */
  IF NOT EXISTS (SELECT _id_ FROM Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog)
    EXEC tSQLt.Fail
      @Message0 = 'Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog is empty! usp_Raiserror_SafetyStockLevel has not been called!';
END;
GO

The UnitTestTRProductSafetyStockLevel.[test try to insert one wrong row] stored procedure implements three sections also known as Arrange, Act and Assert.

The Arrange section contains the call to tSQLt.SpyProcedure that is able to isolate the stored procedure Production.usp_Raiserror_SafetyStockLevel which would return an error within the trigger causing the test to fail systematically. To create independent tests from the objects contained within the System Under Test, we can replace the actions performed by a stored procedure with a “spy”. tSQLt.SpyProcedure allows us to write tests for one procedure in isolation from the others. tSQLt.SpyProcedure will create a table whose name is composed by concatenating the name of the stored procedure we want to isolate and the fixed part “SpyProcedureLog”. This table contains an identity column named “_id_” and a column for each parameter of spied on procedure. SpyProcedure can replace the actions performed by the stored procedure indicated in the @ProcedureName parameter with the command provided in the @CommandToExecute parameter. Each time the spied procedure is invoked during the test, instead of executing the procedure, a new log line will be created in the @ProcedureName_SpyProcedureLog table. If specified, the command contained in the @CommandToExecute parameter will be invoked.

The Act section contains the INSERT command which will start the trigger being tested. The value specified for the SafetyStockLevel column is less than 10 so we expect the trigger will prevent the insertion.

The Assert section contains the call to tSQLt.Fail stored procedure, it will be executed if the log table Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog is empty.

To run our first test, it will be necessary to invoke tSQLt.Run specifying the name of the unit test to be run… the output will be similar to the following one:

The trigger prevented the insertion of a new product with a value of safety stock lower than 10, the test returned a positive result. Let’s proceed with writing the other unit tests…

USE [AdventureWorks2017];
GOCREATE PROCEDURE UnitTestTRProductSafetyStockLevel.[test try to insert one right row]
AS
BEGIN
  /*
    Arrange:
    Spy the procedure Production.usp_Raiserror_SafetyStockLevel
  */
  EXEC tSQLt.SpyProcedure 'Production.usp_Raiserror_SafetyStockLevel';  /*
    Act:
    Try to insert one right row with SafetyStockLevel lower than 10
  */
  INSERT INTO Production.Product
  (
    [Name]
    ,ProductNumber
    ,MakeFlag
    ,FinishedGoodsFlag
    ,SafetyStockLevel
    ,ReorderPoint
    ,StandardCost
    ,ListPrice
    ,DaysToManufacture
    ,SellStartDate
    ,rowguid
    ,ModifiedDate
  )
  VALUES
  (
    N'Carbon Bar 1'
    ,N'CB-0001'
    ,0
    ,0
    ,20 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  );  /*
    Assert
  */
  IF EXISTS (SELECT _id_ FROM Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog)
    EXEC tSQLt.Fail
      @Message0 = 'Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog is not empty! The value assigned to Safety Stock Level is right, it is greater than 10!';
END;CREATE PROCEDURE UnitTestTRProductSafetyStockLevel.[test try to insert multiple rows]
AS
BEGIN
  /*
    Arrange:
    Spy the procedure Production.usp_raiserror_safety_stock_level
  */
  EXEC tSQLt.SpyProcedure 'Production.usp_Raiserror_SafetyStockLevel';  /*
    Act:
    Try to insert multiple rows
    The first product has a wrong value for SafetyStockLevel column,
    whereas the value in second one is right
  */
  INSERT INTO Production.Product
  (
    [Name]
    ,ProductNumber
    ,MakeFlag
    ,FinishedGoodsFlag
    ,SafetyStockLevel
    ,ReorderPoint
    ,StandardCost
    ,ListPrice
    ,DaysToManufacture
    ,SellStartDate
    ,rowguid
    ,ModifiedDate
  )
  VALUES
  (
    N'Carbon Bar 1'
    ,N'CB-0001'
    ,0
    ,0
    ,9 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  ),
  (
    N'Carbon Bar 3'
    ,N'CB-0003'
    ,0
    ,0
    ,15 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  );  /*
    Assert
  */
  IF NOT EXISTS (SELECT _id_ FROM Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog)
    EXEC tSQLt.Fail
      @Message0 = 'Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog is empty! usp_Raiserror_SafetyStockLevel has not been called!';
END;CREATE PROCEDURE UnitTestTRProductSafetyStockLevel.[test try to insert multiple rows ordered]
AS
BEGIN
  /*
    Arrange:
    Spy the procedure Production.usp_Raiserror_SafetyStockLevel
  */
  EXEC tSQLt.SpyProcedure 'Production.usp_Raiserror_SafetyStockLevel';  /*
    Act:
    Try to insert multiple rows
    The first product has a right value for SafetyStockLevel column,
    whereas the value in second one is wrong
  */
  INSERT INTO Production.Product
  (
    [Name]
    ,ProductNumber
    ,MakeFlag
    ,FinishedGoodsFlag
    ,SafetyStockLevel
    ,ReorderPoint
    ,StandardCost
    ,ListPrice
    ,DaysToManufacture
    ,SellStartDate
    ,rowguid
    ,ModifiedDate
  )
  VALUES
  (
    N'Carbon Bar 1'
    ,N'CB-0001'
    ,0
    ,0
    ,15 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  ),
  (
    N'Carbon Bar 3'
    ,N'CB-0003'
    ,0
    ,0
    ,3 /* SafetyStockLevel */
    ,750
    ,0.0000
    ,78.0000
    ,0
    ,GETDATE()
    ,NEWID()
    ,GETDATE()
  );  /*
    Assert
  */
  IF NOT EXISTS (SELECT _id_ FROM Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog)
    EXEC tSQLt.Fail
      @Message0 = 'Production.usp_Raiserror_SafetyStockLevel_SpyProcedureLog is empty! usp_Raiserror_SafetyStockLevel has not been called!';
END;
GO

All unit tests have been created. Using the tSQLt.RunTestClass procedure with the name of the test class we will be able to run all tests contained in.

USE [AdventureWorks2017];
GO-- Run all tests in the class
EXEC tSQLt.RunTestClass 'UnitTestTRProductSafetyStockLevel';
GO

The output will be the same to the one shown below.

The fourth test failed! The trigger prevents the insertion of new products (multiple products in the “all at once” mode) only if they are ordered in this way: The safety stock value less than 10 must be in the first row, for the first product. Did you ever imagine that? The ordering of products in the multiple inserts can elude the trigger check, in this case incorrect data will be saved! When the virtual table “Inserted” will contain only one row, the trigger will work properly, in the other cases we will have no guarantees!

Observing the value assumed by the variables during the execution (for more details you can read the article How to debug a Trigger), the bug is easily understood. The @SafetyStockLevel variable can store only one value at a time, the check on it will assume valid or invalid all the other values depending on the result obtained for the first row. Thanks to the execution of the tests, we found a bug before releasing the trigger to the customer!

We modify the trigger as described below to fix the bug saw during the execution of the unit tests.

USE [AdventureWorks2017];
GOCREATE OR ALTER TRIGGER Production.TR_Product_StockLevel ON Production.Product
AFTER INSERT AS
BEGIN
  /* 
     Avoid to insert products safety stock level lower than 10
  */  -- Testing all rows in the Inserted virtual table
  IF EXISTS (
             SELECT ProductID
             FROM inserted
             WHERE (SafetyStockLevel < 10)
            )
    -- Error!!
    EXEC Production.usp_Raiserror_SafetyStockLevel
      @Message = 'Safety stock level cannot be lower than 10!';
END;
GO

The advantage of having written the unit tests is now clear: The unit tests not only allowed us to detect a bug but, they also give us the serenity and security of being able to retry the test cases to verify that changes made to fix the bug have not introduced anomalies. We can retry all test cases by invoking tSQLt.RunTestClass as follows:

USE [AdventureWorks2017];
GO-- Run all tests in the class
EXEC tSQLt.RunTestClass 'UnitTestTRProductSafetyStockLevel';
GO

The output will be the same to the one shown below.

Summary

In this article we have described how to write our first unit test. The trigger we have chosen is intentionally simple. Think about triggers, stored procedures and complex functions you wrote (those with more than 200 lines of code to be clear), would you feel safe in modifying them? If the answer is “No” the first thing to do is to write the unit tests, now you know how!

In the next article we will deepen the concept of “test in isolation” … in the meantime, enjoy your first unit test!