How to use fake functions with SQL unit testing?

In this article series, we are exploring SQL unit testing, in general, and also we are reinforcing details and topics with various practical examples. We are using the tSQLt framework because it is the quite powerful and handy tool in order to develop and code SQL unit tests.

Note: I suggested that to take a glance on the previous articles (see the TOC at bottom) which are related to SQL unit testing. Especially SQL unit testing with the tSQLt framework for beginners can be a very good starting point for the newbies in SQL unit testing and tSQLt frameworks.

All these articles underscore two essential benefits of the SQL unit testing;

SQL unit testing improves code quality and design
SQL unit testing enables early bug detection and defection

and also these articles mentioned about two basic principles about SQL unit testing;

SQL unit test must test only one individual, functional and atomic part of the code
If it is possible, we should isolate the SQL unit test from dependencies

Moving from these ideas, there is no doubt about it, dependency isolation is a significantly valuable technique used to develop a more precise SQL unit tests. That’s why, in this article we will continue to learn new faking methods of the tSQLt framework. Particularly, we will try to find out the answer of the how to use fake functions in SQL unit testing. Actually, the answer to the question is not very complicated. The tSQLt framework offers a method called the FakeFunction, we can also the use FakeFunction in the SQL unit tests to handle these type of issues. Let learn more details about it.

Overview about FakeFunction

Most often, a tested case can be quite complicated, and it may also include function(s). Under this circumstance the function result may influence the SQL unit testing behaviors because the tested condition will be dependent on the function. However, this is the worst case scenario which we do not want to see in this SQL unit testing. This violates the unit test dependency isolation pattern.

Now we will go through a demonstration and we will explore how to use a fake function in the SQL unit testing. The following scalar-valued function makes some modulo calculations and then returns the result as odd or even.

CREATE OR ALTER FUNCTION dbo.UDefFuncOddorEven (@n int)

RETURNS bit

BEGIN

DECLARE @ModuleRes INT

SET @ModuleRes = (@n%11)

SET @ModuleRes = (@n%9)

RETURN (@ModuleRes % 2)

END

Now we will take a glance at the below, the stored procedure that uses this scalar-valued function and the result set of the stored procedure is directly affecting from this scalar-valued function result.

DROP TABLE IF EXISTS OrderOnline

CREATE TABLE OrderOnline

(Id INT PRIMARY KEY IDENTITY(1,1),

OrderName VARCHAR(100),

CustomerName VARCHAR(100))

CREATE OR ALTER PROC SetOrders

@OName AS VARCHAR(100),

@CName AS VARCHAR(100)

BEGIN

DECLARE @RandomVal AS INT

SET @RandomVal= FLOOR(RAND()*1000)

DECLARE @ufResult AS BIT

SELECT @ufResult=dbo.UDefFuncOddorEven(@RandomVal)

IF @ufResult=1

BEGIN

INSERT INTO OrderOnline (OrderName,CustomerName) VALUES (@OName,@CName)

END

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='OrderOnline'

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='SetOrders'

At first, we will examine the code flow of the query. SetOrders stored procedure generates a random number and then it determines the insert operation according to UDefFuncOddorEven scalar-valued function result of this random number. In fact, our main purpose is to check functionality of the SetOrders stored procedure, but it is SetOrders has a strong dependency to UDefFuncOddorEven function and it is directly affects code flow of the SetOrders. Shortly, we must handle dependency issue. The FakeFunction method allows us to the change original function with a dummy or faux one during the SQL unit test process so that we can determine and take the control of the concerned function result. As a general rule, fake functions return values can be hard-coded so that we can be sure about the return value and this touch makes it very simple. Now we will go through other details and usage of the FakeFunction.

FakeFunction syntax

FakeFunction takes two parameters as follows;

1 2	tSQLt.FakeFunction [@FunctionName = ] 'function name' , [@FakeFunctionName = ] 'fake function name'

@FunctionName parameter specifies the original function name which we want to replace with the fake one
@FakeFunctionName parameter specifies the fake function name that replaces the original function name during the SQL unit test period

These parameters data types are nvarchar(max).

What does the fake function look like?

In this section we will code the SQL unit test with the help of the tSQLt framework. At first, we will create the fake function which we will use instead of the original one. We should create the fake function as simple as possible in order to prevent complexity of the SQL unit test. The first choice would be a function which returns a hard coded value so that we don’t worry about the return value. At this point, we will notice one thing about fake function naming convention. We should add a fake expression to the name of the fake function so that we can easily recognize this function and also we can add the return value of the fake function to the name of it. Let’s create our first fake function.

CREATE OR ALTER FUNCTION dbo.UDefFuncOddorEven_Fake_Return_1 (@n int)

RETURNS bit

BEGIN

RETURN 1

END

As you can see, we replace the complex modulo calculation part of the original scalar-valued function with a very simple hard-coded return value so that fake function every time returns the same value and we know that.

Fake function usage in SQL unit testing.

In this SQL unit test example, we want to successfully check SetOrders stored procedure functionality, so, does it insert the insert proper values to OrderOnlLine table? Mainly because we want to test this situation. Now we will reference this idea and then write the SQL unit test through the tSQLt framework, it will look like the following query.

EXECUTE tsqlt.NewTestClass 'TestFakeFunction'

EXECUTE tSQLt.NewTestClass 'TestFakeFunction'

CREATE OR ALTER PROCEDURE TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]

BEGIN

DROP TABLE IF EXISTS expected

DROP TABLE IF EXISTS actual

EXEC tSQLt.FakeTable 'OrderOnline'

SELECT TOP(0) * INTO expected FROM OrderOnline

SELECT TOP(0) * INTO actual FROM OrderOnline

EXEC tSQLt.FakeFunction 'dbo.UDefFuncOddorEven' ,'dbo.UDefFuncOddorEven_Fake_Return_1'

INSERT INTO expected

(OrderName , CustomerName)

VALUES ('Pizza','Ryan Romero')

EXECUTE SetOrders 'Pizza' ,'Ryan Romero'

INSERT INTO actual

SELECT * FROM OrderOnline

EXEC tSQLt.AssertEqualsTable expected,actual

END

EXEC tSQLt.Run 'TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]'

Now we will tackle the SQL unit test line by line and make a comprehensive explanation.

Create a fake table

1	EXEC tSQLt.FakeTable 'OrderOnline'

In this part of the query, we create a fake of the OrderOnline table because this table should be empty during the unit test so that it contains the rows which we inserted. Actually, we break the dependency of the SetOrders stored procedure to OrderOnline table.

Create expected and actual tables

1 2	SELECT TOP(0) * INTO expected FROM OrderOnline SELECT TOP(0) * INTO actual FROM OrderOnline

At the end of the SQL unit test, we compare the expected and actual table’s rows with help of the tsqlt.AssertTables expression. For this reason, we have to create expected and actual tables. The simplest way to perform table creation is to derive from the fake OrderOnline table.

Fake function

1	EXEC tSQLt.FakeFunction 'dbo.UDefFuncOddorEven' ,'dbo.UDefFuncOddorEven_Fake_Return_1'

In this part of the unit test, we replace original function with the fake one so that we know fake ones always return the same hard-coded value, we design SQL unit test according to the certainty.

Populate values to expected table

INSERT INTO expected

(OrderName , CustomerName)

VALUES ('Pizza','Ryan Romero')

In this part of the SQL unit test, we populate values into expected table.

Populate values to actual table

EXECUTE SetOrders 'Pizza' ,'Ryan Romero'

INSERT INTO actual

SELECT * FROM OrderOnline

In this part of the query, when we execute the SetOrders procedure, it inserts values to the fake of the OrderOnline table and we populate these values into the actual table.

SQL unit testing result

1	EXEC tSQLt.Run 'TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]'

This is the final and the most important step because the result of the SQL unit test is being determined at this point and according to expected and actual table’s comparison. If these tables’ values do not match, tSQLt framework unit test will be a return fail.

Attach importance to FakeFunction errors.

We must consider some points about FakeFunction, if the FakeFunction method does not find the original or the fake one, it returns an error which likes the below.

[function_name or fake_function_name] does not exist! (This includes the return type for scalar functions.)

Another point which we should consider about FakeFunction is that, the original function and fake function parameters have to match otherwise we will experience the following error.

Parameters of both functions must match! (This includes the return type for scalar functions.)

Wrapping Up

In this article we continued with our journey about SQL unit testing and tSQLt framework. At the same time, we learnt how to use fake functions in the SQL unit tests in order to isolate dependency of the functions. We demonstrated a very simple SQL unit test which used the FakeFunction method and also we highlighted all details of the aforementioned example.

SQL Unit Testing with SSDT (SQL Server Data Tools)