Esat Erkec
SQL unit testing - Result image of FakeFunction usage

How to use fake functions with SQL unit testing?

April 30, 2019 by

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.

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.

Test stored procedure for SQL unit testing

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;

  • @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.

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.

SQL unit testing - Result image of FakeFunction usage

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

Create a fake table

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

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

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

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

Populate values to actual table

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

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.)

SQL Unit testing FakeFunction does not exists error image

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.)

FakeFunction parameters of both functions must match error image.

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.

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

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
Esat Erkec

Latest posts by Esat Erkec (see all)

656 Views