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.
|
1 2 3 4 5 |
exec sp_configure 'show advanced options',1 Go Reconfigure go sp_configure 'Database Mail XPs' |
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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
DROP TABLE IF EXISTS Orders; GO CREATE TABLE Orders (ClientName VARCHAR(50), ClientMail VARCHAR(50), OrderId UNIQUEIDENTIFIER DEFAULT(NEWID()) ); GO CREATE OR ALTER PROCEDURE SendOrderMail (@OrderMail AS VARCHAR(50), @OrderId AS UNIQUEIDENTIFIER ) AS DECLARE @MailSubject AS VARCHAR(300); DECLARE @MailBody AS VARCHAR(300); SET @MailSubject = CONCAT(CAST(@OrderId AS VARCHAR(50)), ' order information'); SET @MailBody = CONCAT(CAST(@OrderId AS VARCHAR(50)), ' order is taken'); EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mymail', @recipients = @OrderMail, @subject = @MailSubject, @body = @MailBody; GO CREATE OR ALTER PROCEDURE InsertOrder (@CName AS VARCHAR(50), @CMail VARCHAR(50), @OrderId AS UNIQUEIDENTIFIER ) AS INSERT INTO Orders (ClientName, ClientMail, OrderId ) VALUES (@CName, @CMail, @OrderId ); EXEC SendOrderMail @CMail, @OrderId |
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:
|
1 2 |
tSQLt.SpyProcedure [@ProcedureName = ] 'procedure name' [, [@CommandToExecute = ] 'command' ] |
@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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
EXECUTE tSQLt.NewTestClass 'TestOrderProc'; GO CREATE OR ALTER PROCEDURE TestOrderProc.[test InsertOrder stored procedure insert Order table] AS DROP TABLE IF EXISTS actual; DROP TABLE IF EXISTS expected; EXEC tSQLt.FakeTable 'Orders'; SELECT middle 0 * INTO actual FROM Orders; SELECT middle 0 * INTO expected FROM Orders; EXEC tSQLt.SpyProcedure 'SendOrderMail'; INSERT INTO expected (ClientName, ClientMail, OrderId ) VALUES ('Popeye', 'spinach@spinach.com', '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3' ); EXECUTE InsertOrder 'Popeye', 'spinach@spinach.com', '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3'; INSERT INTO actual SELECT * FROM Orders; EXECUTE tSQLt.AssertEqualsTable 'expected', 'actual'; GO EXECUTE tsqlt.Run 'TestOrderProc.[test InsertOrder stored procedure insert Order table]'; |
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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
EXECUTE tSQLt.NewTestClass 'TestOrderProc'; GO CREATE OR ALTER PROCEDURE TestOrderProc.[test InsertOrder stored procedure insert Order table] AS DROP TABLE IF EXISTS actual; DROP TABLE IF EXISTS expected; EXEC tSQLt.FakeTable 'Orders'; SELECT middle 0 * INTO actual FROM Orders; SELECT middle 0 * INTO expected FROM Orders; EXEC tSQLt.SpyProcedure 'SendOrderMail'; INSERT INTO expected (ClientName, ClientMail, OrderId ) VALUES ('Popeye', 'spinach@spinach.com', '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3' ); EXECUTE InsertOrder 'Popeye', 'spinach@spinach.com', '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3'; SELECT * FROM SendOrderMail_SpyProcedureLog; INSERT INTO actual SELECT * FROM Orders; EXECUTE tSQLt.AssertEqualsTable 'expected', 'actual'; GO EXECUTE tsqlt.Run 'TestOrderProc.[test InsertOrder stored procedure insert Order table]'; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROCEDURE CalcTempSize(@Size AS BIGINT OUT) AS SELECT @Size = SUM(size) / 128 FROM tempdb.sys.database_files; RETURN @Size; GO CREATE OR ALTER PROCEDURE TempSizeAlert(@AlertMessage AS VARCHAR(500) OUT) AS DECLARE @TempDbSize AS BIGINT; EXECUTE CalcTempSize @Size = @TempDbSize OUT; IF @TempDbSize > 512 BEGIN SET @AlertMessage = CONCAT('tempdb size is ', @TempDbSize); END; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
EXECUTE tSQLt.NewTestClass 'TempdbAlert'; GO CREATE OR ALTER PROCEDURE TempdbAlert.[Test TempSizeAlert_StoredProcedure Alert Message Val_555] AS DECLARE @expectedmessage AS VARCHAR(500)= 'tempdb size is 555'; DECLARE @actualmessage AS VARCHAR(500); EXECUTE tSQLt.SpyProcedure 'dbo.CalcTempSize', 'SET @Size=555'; EXECUTE TempSizeAlert @AlertMessage = @actualmessage OUT; EXECUTE tSQLt.AssertEquals @expectedmessage, @actualmessage; GO EXECUTE tsqlt.Run 'TempdbAlert.[Test TempSizeAlert_StoredProcedure Alert Message Val_555]'; |
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.
Conclusion
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
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023