SQL Unit Testing Using tSQLt: Part 3


The Journey Continues!

Welcome back to the tSQLt series, where we are covering the fundamentals of Unit Testing within SQL Server by using the tSQLt Framework.

Today we are progressing onward by discussing some of the useful features within tSQLt to ensure our tests are accurate, reliable, and repeatable.

In the last post, we went through the process of creating a basic tSQLt test. We covered the basic constructs of generating a unit test by implementing the three stages of a test:

  1. Assemble
  2. Act
  3. Assert

Within the Assemble stage, we made use of the Fake Table function, which allowed us to create a ‘faked’ version of a table. Such functionality is a core pillar of unit testing with tSQLt. Our unit tests should be isolated and idempotent mechanisms.

The Fake Table function is just one of the many tools in our arsenal when using tSQLt, and I wanted to take some time in this post to cover another feature which will be useful to us - the Fake Function function. More official documentation about it here - Fake Function

From tSQLt.org:

Code that calls a function can be difficult to test if that function performs significant logic. We want to isolate the code we are testing from the logic buried in the functions that it calls. To create independent tests, we can replace a called function with a fake function. The fake function will perform much simpler logic that supports the purpose of our test. Often, the fake function will simply return a hard-coded value.


In this post, we’re going to walk through a demo to illustrate the following:

  • The underlying procedure being tested
  • The Unit Test, which will test the underlying procedure
  • The implementation of the Fake Function feature

Setting the Scene - The procedure to be tested

We have an updated procedure, from our last post, called - GetUserDetail_LastYear

The key things this procedure does:

  • Takes in one input parameter called @DisplayName
  • Retrieves information about the passed-in user, for the past year, based on the time the procedure is run (take note of this, trusted reader)

Once again, we’re going to be using the Stack Overflow database. Here is the full procedure:

 1CREATE OR ALTER PROCEDURE [dbo].[GetUserDetail_LastYear]
 2	@DisplayName NVARCHAR(100)
 3AS
 4BEGIN
 5
 6    DECLARE @Now            DATETIME
 7    DECLARE @OneYearAgo     DATETIME
 8
 9    SET @Now                = dbo.DateTimeNow()
10    SET @OneYearAgo         = DATEADD(YEAR, -1, @Now)
11
12    SELECT
13        u.DisplayName,
14        u.Location,
15        u.Reputation,
16        COUNT(c.Id) AS CommentCount
17    FROM dbo.Users u
18    INNER JOIN dbo.Comments c ON u.Id = c.UserId
19    WHERE 
20        u.DisplayName = @DisplayName AND 
21		c.CreationDate BETWEEN @OneYearAgo AND @Now
22    GROUP BY u.DisplayName, u.Location, u.Reputation
23END

Calling the procedure for a particular user renders the following detail:

1EXEC dbo.GetUserDetail_LastYear @DisplayName = 'Jon Skeet'

tsqlt



Setting the Scene - The Unit Test

Following the same methodology used in the last post, we have created a unit test for dbo.GetUserDetail_LastYear.

Here is the full unit test, we will discuss it in more detail later:

 1CREATE OR ALTER PROCEDURE [tests].[test GetUserDetail_LastYear]
 2AS
 3BEGIN
 4
 5DECLARE 
 6	@UserName NVARCHAR(100)	= 'Jon Skeet'
 7
 8
 9/* ASSEMBLE STAGE */
10
11EXEC tSQLt.FakeTable 'dbo.Users'
12
13INSERT INTO dbo.Users
14(Id,DisplayName, Location, Reputation)
15VALUES
16	(22656,	'Jon Skeet'		,'Reading, United Kingdom'		,1047863),
17	(22661,	'Virne'			,'Finland'						,1076),
18	(22668,	'Dickon Reed'	,'Cambridge, United Kingdom'	,2237)
19
20
21EXEC tSQLt.FakeTable 'dbo.Comments'
22
23INSERT INTO dbo.Comments
24(Id, UserId,CreationDate)
25VALUES
26	(436275	, 22661 , '2020-05-01 09:39:00.000'),
27	(197827	, 22668 , '2024-09-26 16:35:51.177'),
28	(37360	, 22656	, '2024-09-26 16:35:51.177'),
29	(37585	, 22656	, '2025-01-26 16:35:17.310'),
30	(36000	, 22656	, '2022-08-26 19:21:42.357')
31
32
33CREATE TABLE dbo.Expected
34(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
35
36
37CREATE TABLE dbo.Actual
38(DisplayName NVARCHAR(100), Location NVARCHAR(200),Reputation INT, CommentCount INT)
39
40
41INSERT INTO dbo.Expected
42(DisplayName,Location, Reputation, CommentCount)
43VALUES
44	('Jon Skeet',	'Reading, United Kingdom',	1047863,	2)
45
46
47/* ACT STAGE */
48
49INSERT INTO dbo.Actual
50
51EXEC [dbo].[GetUserDetail_LastYear] 
52	@DisplayName	= 'Jon Skeet'
53
54
55/* ASSERT STAGE */
56
57EXEC tSQLt.AssertEqualsTable
58	@Expected	= 'dbo.Expected',
59	@Actual		= 'dbo.Actual',
60	@FailMsg	= 'Our test failed!'
61
62END

So we now have our underlying stored procedure, we also have our associated unit test. If we call our test:

1EXEC tSQLt.Run @TestName = '[tests].[test GetUserDetail_LastYear]'

The test passes, as expected:

tsqlt

All is well, the test passes and our work here is done. Right?


WRONG.

cavill_meme



Issue 1: The Date Function Used

Currently, if we run our new unit test, it runs without issue, for ‘today’. The eagle-eyed readers may have noticed something within our main stored procedure.

Within our stored procedure, we have the following function being used:

1SET @Now = dbo.DateTimeNow()

This function is calculating the date and time when the procedure is executed. We have written our unit test to calculate the results to accommodate this, for ’today’.
But what happens if we run it tomorrow? Next week? You guessed it, our test will fail because the date will obviously be different, and so will our date range that’s being used as a filter. We are currently expecting a comment count of 2 to come back, based on the date the test is executed (today). This will obviously change over time and so, cannot be sure that our unit test will continue to pass.

Thankfully, the good folks over at tSQLt have thought of this and have a useful function for us to handle these scenarios. That function is the Fake Function.


Solution 1: The Fake Function

The Fake Function option allows us to tell tSQLt that whenever it encounters a particular function, it should ignore it and use something else. The ‘something else’ is where we can step in and become empowered to handle this scenario.

We will add the following to our unit test code, within the “Assemble” section:

1EXEC tSQLt.FakeFunction @FunctionName = 'dbo.DateTimeNow', @FakeFunctionName = 'Fake.DateTimeNow'

As above, we are calling the Fake Function option to say whenever we encounter the function dbo.DateTimeNow, that we should actually use the function ‘Fake.DateTimeNow’ instead.

This allows us to use a ‘faked’ version of the function. Here is our faked version:

1CREATE OR ALTER FUNCTION [Fake].[DateTimeNow]()
2	RETURNS DATETIME
3AS
4BEGIN
5	RETURN '2025-04-11 00:00:00.000'
6END
7GO

This version simply returns a static datetime which is perfect for us to be able to generate a repeatable method for interrogating the data when running our unit test.

This is ideal, as our focus isn’t on testing the underlying functionality of the dbo.DateTimeNow function at this stage. We are only concerned with the isolated test of the stored procedure dbo.GetUserDetail_LastYear. We can now be confident that whenever our unit test is executed, it’s always going to use the same date to interrogate the data.

Should we need to, we can create a dedicated unit test for the actual function at a later date, in isolation.

Our updated unit test, now begins like this:

 1CREATE OR ALTER PROCEDURE [tests].[test GetUserDetail_LastYear]
 2AS
 3BEGIN
 4
 5DECLARE 
 6	@UserName NVARCHAR(100)	= 'Jon Skeet'
 7
 8
 9/* ASSEMBLE STAGE */
10
11EXEC tSQLt.FakeFunction @FunctionName = 'dbo.DateTimeNow', @FakeFunctionName = 'Fake.DateTimeNow'
12
13
14EXEC tSQLt.FakeTable 'dbo.Users'
15
16INSERT INTO dbo.Users
17(Id,DisplayName, Location, Reputation)
18VALUES
19	(22656,	'Jon Skeet'	,'Reading, United Kingdom'	,1047863),
20	(22661,	'Virne'		,'Finland'			,1076),
21	(22668,	'Dickon Reed'	,'Cambridge, United Kingdom'	,2237)
22
23...
24
25/* TRUNCATED FOR BREVITY */

I hope you enjoyed this post that covered the Fake Function option of tSQLt. Once again, this is just one of many useful tools we can access when using the tSQLt framework. There are many more which we will continue to cover in the next post in the series. Speaking of which, next up, we will cover ‘Spy Procedure’ feature of tSQLt. I hope to see you there.

Thanks for reading.