Esat Erkec
SQL Unit testing results with tSQLt

SQL Unit Testing Mocking Stored Procedures

May 8, 2019 by

In this SQL Unit testing article, we will continue our journey with SQL unit testing. In the previous articles of this series (see the TOC at the bottom), we mentioned about SQL unit testing essentials and designing approaches as well and then we reinforce these notions with various tSQLt framework practical examples. In this article, we will continue to learn how to mock stored procedures in the tSQLt framework and we will provide it with two examples.

Note: I want to suggest that if you don’t have any knowledge or idea about SQL unit testing please read the SQL unit testing with the tSQLt framework for beginners article and also you can take glance at other articles for more details (see the TOC at the bottom) about SQL unit testing and tSQLt framework.

In some cases, a stored procedure, which we want to test, includes and invokes another stored procedure(s). The crucial point here is that, the stored procedure to be tested might depend on the invoked one. Also, the result of the stored procedure which is invoked can directly influence the unit test result. There is no doubt in that, if we try to write a unit test without eliminating the dependency issue, it might create a more complicated and fragile SQL unit test. It would be really tedious to struggle this type of SQL unit test. On the other hand, unit test maintenance and management operations will require more effort than usual. Certainly, these type of unit test designs are located opposed side to the unit test design concept.

Briefly, if a stored procedure, to be tested, invokes another one and the invoked stored procedure result has an influence upon the unit test, we must isolate it in the unit test. For this reason, we will learn how to eliminate these type of dependencies in the tSQLt framework. Now, let’s learn how to deal with this issue in tSQLt framework;

Overview of SpyProcedure

tSQLt framework provides us a fantastic method which is called as SpyProcedure. SpyProcedure method creates a mock or in other words spy ((Test spy allow us to store methods execution for the later verification) procedure. During the SQL unit test mock procedure sit in for the original one and imitate it. At the same time, SpyProcedure stores the parameter values in a special table.

Note: In the following test scenario, we are supposing that the Database Mail configuration is disabled. You can check it with the following query, but do not disable it in the production environment without being sure.

Let’s try to explain the usage of the SpyProcedure with an example. Imagine that we want to test a stored procedure and this store procedure inserts client orders to a table. If the insert process succeeds, it sends an email to the clients through the sp_send_dbmail system stored procedure. However, in the development environment, Database Mail is not configured due to security considerations. Under these circumstances, if we want to write a SQL unit test of the stored procedure, we need to eliminate the dependency of the database mail configuration. Otherwise, it returns an error regarding to database mail configuration. In this scenario, the stored procedures will look like the following;

As you can see, InsertOrder stored procedure performs an insert operation and then it executes the SendOrderMail. Stored procedure uses sp_send_dbmail system procedure in order to send an email. Under these circumstances, if we want to test InsertOrder functionality, the best method is to eliminate SendOrderMail procedure dependency.

SpyProcedure usage the SQL unit testing

SpyProcedure takes two parameters:

@ProcedureName parameter specifies the procedure name which we want to mock.

@CommandToExecute is an optional parameter which helps us to execute a command when the procedure is invoked.

At first, we will write the unit test and then we will handle it line by line.

SQL Unit testing results with tSQLt

In the following table, we can find out all the SQL unit test query parts detailed explanations. These explanations are very important to understand logic of the SpyProcedure.

We create a new test class
We give an understandable name to test method
In this part of the query, we create the fake Orders table and then we derive the actual and expected table based on our fake Orders table
We mock the SendOrderMail stored procedure so that we avoid the dependency of it
We populate the actual and expected tables. The reason for doing so is that we want to test insert functionality of the InsertOrder stored procedure
This is the final step of the unit test and in this step, we compare the expected and actual tables. In this way, we learn if the insert functionality of the stored procedure is working correctly

After all these comprehensive explanations, I want to mark one point; if the mocked stored procedure invoked the tSQLt framework. Then, it creates a special table and it stores the called parameters into this table. The table name is created by adding “_SpyProcedureLog” prefix to the end of the mocked stored procedure. This name is based like this SendOrderMail_SpyProcedureLog for the previous example. Now, we will prove it in the following example;

SQL Unit testing results with tSQLt using SpyProcedureLog

As you can see, the OrderMail and OrderId parameter values stored to the SendOrderMail_SpyProcedureLog table. According to your SQL unit test case, you can use this table. The purpose of this method of use is to illustrate only the functionality of the SpyProcedureLog table.

SpyProcedure advanced usage in the SQL unit testing

In the SpyProcedure usage in the SQL unit testing section, we mentioned about an optional parameter whose name is @CommandToExecute. Now we will explore this parameter’s usage in the tSQLt framework. Suppose that, we have a stored procedure and it generates an alert message when the tempdb size over a value. Our aim is to test the accuracy of the alert message in the unit test. However, tempdb size calculates by another procedure so we should manipulate the return value of this stored procedure. At first, we will create the stored procedures with the following queries:

As you can see in the above, CalcTempSize calculates the tempdb file size and TempSizeAlert creates an alert message. The crucial point is that, unless CalcTempSize does not return a value over 512, the TempSizeAlert stored procedure does not create a message. Let’s create the SQL unit test and execute it.

Now, we will handle the previous SQL unit test line by line;

We create a new test class
We declare the expected and actual values
We manipulate the CalcTempSize output value so that we obtain the desired value
We assign the stored procedure return value to actual value
We compare the expected and actual message value so that the test result occurs

As a result, we completed two different examples to test the usage of the SpyProcedure. In the first one, we isolated the dependency and in the second one, we manipulated the output of the mocked stored procedure. According to the SQL unit test cases, you can determine usage method of the SpyProcedure.


The idea behind of the mocking object is very basic, the database objects should be tested without their dependencies, so it means that the dependencies must be eliminated in the unit tests. The tSQLt framework allows us various method to achieve this idea us, however in this article particularly we learned how to mock stored procedure and usage of the SpyProcedure method. SpyProcedure provides a huge advantage and adds flexibility for our unit tests. Above all, we can create isolated, induvial and independent SQL unit tests. Because of features and capability like this, IMHO, tSQLt framework is the best utility to create SQL unit tests.

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
Latest posts by Esat Erkec (see all)