This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it.
In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure and the main procedure needs to be unit tested in order to make sure that the requirements are met. The key is to ensure that a unit test can only be written for a single unit of code which means we need one unit test for the main procedure and another unit test for the utility procedure.
Unit testing a single stored procedure is easier as compared to unit testing a procedure which calls a utility procedure inside its code.
It is very important to understand the utility procedure scenario and why it is different than unit testing a normal stored procedure.
Scenario: Utility Procedure within Main Procedure
In order to understand utility procedure scenario let us begin by the definition and example of utility procedure:
What is Utility Procedure
A utility procedure is generally a small procedure which is used by the main procedure(s) to do some specific task such as getting something for the main procedure or adding something to the main procedure.
Another definition of utility procedure is a small stored procedure written for maintenance purposes which may involve system tables or views to be called by any number of procedures or even directly.
Examples of Utility Procedure
Think of a customer-order-product scenario where a customer places an order for a particular product. If we create the main procedure to get us all the orders placed by a particular customer then a utility procedure can be used to help us understand whether each order was placed by the customer on weekday or weekend.
In this way, a small utility procedure can be written to return “Weekday” or “Weekend” based on the date the product was ordered by the customer.
Another example can be system stored procedures such as “sp_server_info” in the master database which gives SQL Server installed version information:
Why Unit Testing Utility Procedure is Different
As discussed earlier, unit testing a utility procedure which is called inside the main procedure is slightly complicated than unit testing a simple stored procedure.
Considering customer-order-product example mentioned above, we need to write a unit test to check utility procedure is working fine and also a unit test has to be written to check the main procedure which calls utility procedure is also functioning properly as well as meeting the business requirement(s).
This is illustrated as follows:
Isolating from Utility/Main Procedure Challenge
The main challenge in writing a unit test(s) for the procedure which involves a utility procedure is to make sure that we should not worry about the functioning of utility procedure when writing a unit test for the main procedure and same is true for utility procedure. This is a challenging task which must be kept in mind while writing unit tests for such a scenario.
Isolating from the utility or main procedure is a must, depending on which procedure is under test. We have to keep the following things in mind in the context of isolating while unit testing:
- Isolating from utility procedure when unit testing the main procedure.
- Isolating from the main procedure when unit testing utility procedure.
Please remember this article is focused on unit testing the main procedure by isolating it from its utility procedure.
Creating Main Procedure and its Utility Procedure
In order to write a unit test for a scenario where utility procedure is in use by the main procedure we first need to have the following pre-requisites:
- Sample Database
- Business Requirement(s)
Setup Sample Database (SQLBookShop)
We are creating a simple two-table sample database called “SQLBookShop” which contains the records of all the books ordered as shown below:
Create SQLBookShop sample database as follows:
-- (1) Create SQLBookShop database CREATE DATABASE SQLBookShop; GO
Create and populate database objects (tables) as follows:
USE SQLBookShop; -- (2) Drop book and book order tables if they already exist IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='BookOrder') DROP TABLE dbo.BookOrder IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Book') DROP TABLE dbo.Book IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_TYPE='View' AND t.TABLE_NAME='OrderedBooks') DROP VIEW dbo.OrderedBooks -- (3) Create book table CREATE TABLE Book (BookId INT PRIMARY KEY IDENTITY(1,1), Title VARCHAR(50), Stock INT, Price DECIMAL(10,2), Notes VARCHAR(200) ) -- (4) Create book order table CREATE TABLE dbo.BookOrder (OrderId INT PRIMARY KEY IDENTITY(1,1), OrderDate DATETIME2, BookId INT, Quantity INT, TotalPrice DECIMAL(10,2) ) -- (5) Adding foreign keys for author and article category ALTER TABLE dbo.BookOrder ADD CONSTRAINT FK_Book_BookId FOREIGN KEY (BookId) REFERENCES Book (BookId) -- (6) Populaing book table INSERT INTO dbo.Book (Title, Stock, Price, Notes) VALUES ('Mastering T-SQL in 30 Days', 10, 200, ''), ('SQL Database Reporting Fundamentals', 5, 100, ''), ('Common SQL Mistakes by Developers',15,100,''), ('Optimising SQL Queries',20,200,''), ('Database Development and Testing Tips',30,50,''), ('Test-Driven Database Development (TDDD)',20,200,'') -- (7) Populating book order table INSERT INTO dbo.BookOrder (OrderDate, BookId, Quantity, TotalPrice) VALUES ('2018-01-01', 1, 2, 400), ('2018-01-02', 2, 2, 200), ('2018-01-03', 3, 2, 200), ('2018-02-04', 1, 2, 400), ('2018-02-05', 1, 3, 600), ('2018-02-06', 4, 3, 600), ('2018-03-07', 5, 2, 100), ('2018-03-08', 6, 2, 400), ('2018-04-10', 5, 2, 100), ('2018-04-11', 6, 3, 600); GO -- (8) Creating database view to see all the books ordered by customers CREATE VIEW dbo.OrderedBooks AS SELECT bo.OrderId ,bo.OrderDate ,b.Title ,bo.Quantity ,bo.TotalPrice FROM BookOrder bo INNER JOIN Book b ON bo.BookId = b.BookId
Quick Check – Sample Database
Do a quick database check by running the OrderedBooks view using the following code:
USE SQLBookShop -- Run OrderedBooks view SELECT ob.OrderID ,ob.OrderDate ,ob.Title AS BookTitle ,ob.Quantity ,ob.TotalPrice FROM dbo.OrderedBooks ob
Please note that I am using dbForge Studio for SQL Server so the output look may differ if you run the same code in SSMS (SQL Server Management Studio). However, there is no difference between scripts and their results.
Business Requirement to see the latest order with additional information
A business requirement has been sent to the dev team which states that “The end user wants to know about the most recent order placed for a particular book along with the information whether the order was placed on a weekday or weekend”
A Word about TDDD
We are not strictly following test-driven database development (TDDD) in this article but I strongly recommend to use test-driven database development (TDDD) to create both main and utility procedures which begins by creating a unit test to check if object exists which fails at first, followed by creating the object and rerunning the unit test which must pass.
For a detailed walk-through, please, refer to the first part of this article.
Identifying Utility Procedure
Seeing the business requirement one thing is for sure we need a utility procedure which can tell us whether a particular date is a weekday or a weekend.
Creating Utility Procedure (GetDayType)
Create a utility procedure and call it “GetDayType” as follows:
-- Creating utility procedure to check whether the date passed to it is a weekday or weekend CREATE PROCEDURE dbo.uspGetDayType @OrderDate DATETIME2,@DayType CHAR(7) OUT AS BEGIN SET NOCOUNT ON IF (SELECT DATENAME(WEEKDAY, @OrderDate)) = 'Saturday' OR (SELECT DATENAME(WEEKDAY, @OrderDate)) = 'Sunday' SELECT @DayType= 'Weekend' ELSE SELECT @DayType = 'Weekday' SET NOCOUNT OFF END GO
Quick Check – Utility Procedure
Write the following lines of code to quickly check utility procedure:
-- Quick check utility procedure declare @DayType varchar(10) EXEC uspGetDayType '20181001',@DayType output select @DayType AS [Type of Day]
Creating Main Procedure (GetLatestOrderByBookId)
Create the main procedure to see the most recent order placed for a particular book and also whether the order was placed on a weekday or weekend and call it “GetLatestOrderByBookId” which contains the call for the utility procedure as follows:
-- Creating stored procedure to get most recent order based on bookid and also whether order was placed on weekend or weekday CREATE PROCEDURE dbo.uspGetLatestOrderByBookId @BookId INT AS BEGIN -- Declare variables to store values DECLARE @OrderId INT ,@Book VARCHAR(50) ,@OrderDate DATETIME2 ,@Quantity INT ,@TotalPrice DECIMAL(10, 2) ,@DayType VARCHAR(10) -- Get most recent order for a particular book and initialise variables SELECT TOP 1 @OrderId = bo.OrderId ,@Book = b.Title ,@OrderDate = bo.OrderDate ,@Quantity = bo.Quantity ,@TotalPrice = bo.TotalPrice FROM BookOrder bo INNER JOIN Book b ON bo.BookId = b.BookId WHERE bo.BookId = @BookId ORDER BY OrderDate DESC -- Call utility procedure to get type of day for the above selected most recent order EXEC uspGetDayType @OrderDate ,@DayType OUTPUT -- Show most recent order for a particular book along with the information whether order was placed on weekday or weekend SELECT @OrderId AS OrderId ,@OrderDate AS OrderDate ,@Book AS Book ,@Quantity AS Quantity ,@TotalPrice AS TotalPrice ,@DayType AS DayType END GO
Quick Check – Main Procedure
Run the following code to see if the procedure is working fine or not:
-- Get latest order for the bookid=6 EXEC uspGetLatestOrderByBookId @BookId = 6
Unit Testing Main Procedure Calling Utility Procedure
The key here is to understand the difference between unit testing the main procedure and the utility procedure.
We are currently focused on unit testing the main procedure so this means utility procedure needs to be gracefully isolated from this unit test.
Use of Spy Procedure
In order to make sure that main procedure unit test remains focussed on testing the functionality of main procedure, we have to use spy procedure provided by tSQLt which is going to act as a stub (placeholder) for utility procedure.
According to tsqlt.org, please remember if you are spying a procedure you are not actually unit testing that procedure rather you are making it easier for the other procedure related to the procedure you are spying to be unit tested.
For example, in our case, if we want to unit test main procedure then we have to mock the utility procedure by the use of spy procedure which is going to make easier for us to unit test the main procedure.
Creating Unit Test for Main Procedure Spying Utility Procedure
Create a database unit test to check the main procedure functions properly.
This article works for dbForge Studio for SQL Server (or only dbForge Unit Test) and SSMS (SQL Server Management Studio). However, please note that when using SSMS (SQL Server Management Studio) I assume you have already installed tSQLt Framework and ready to write the unit tests.
To create the first database unit test, right-click the SQLBookShop database. On the shortcut menu, click Unit Test and then Add New Test as follows:
Write the unit test code:
CREATE PROCEDURE GetLatestOrder.[test to check uspGetLatestOrderByBookId outputs correct data] AS BEGIN --Assemble -- Mock order Book and BookOrder table EXEC tSQLt.FakeTable @TableName='dbo.Book' EXEC tSQLt.FakeTable @TableName='dbo.BookOrder' -- Adding mock data to book table INSERT INTO dbo.Book (BookId,Title, Stock, Price, Notes) VALUES (1,'Basics of T-SQL Programming', 10, 100, ''), (2,'Advanced T-SQL Programming', 10, 200, '') -- Adding mock data to bookorder table INSERT INTO dbo.BookOrder (OrderId,OrderDate, BookId, Quantity, TotalPrice) VALUES (1,'2018-01-01', 1, 2, 200), (2,'2018-05-01', 1, 2, 200), (3,'2018-07-01', 2, 2, 400) -- Creating expected table CREATE TABLE GetLatestOrder.Expected ( OrderId INT ,OrderDate DATETIME2 ,Book VARCHAR(50) ,Quantity INT ,TotalPrice DECIMAL(10, 2) ,DayType VARCHAR(10) ) -- Creating actual table CREATE TABLE GetLatestOrder.Actual ( OrderId INT ,OrderDate DATETIME2 ,Book VARCHAR(50) ,Quantity INT ,TotalPrice DECIMAL(10, 2) ,DayType VARCHAR(10) ) -- Creating uspGetDayType spy procedure to isolate main procedure from it so that main procedure can be unit tested EXEC tSQLt.SpyProcedure @ProcedureName = 'dbo.uspGetDayType',@CommandToExecute = 'set @DayType = ''Weekday'' ' -- Inserting expected values to the expected table INSERT INTO GetLatestOrder.Expected (OrderId, OrderDate, Book, Quantity, TotalPrice, DayType) VALUES (2,'2018-05-01', 'Basics of T-SQL Programming', 2, 200,'Weekday'); --Act INSERT INTO GetLatestOrder.Actual EXEC uspGetLatestOrderByBookId @BookId = 1 -- Calling the main procedure --Assert --Compare expected results with actual table results EXEC tSQLt.AssertEqualsTable @Expected = N'GetLatestOrder.Expected', -- nvarchar(max) @Actual = N'GetLatestOrder.Actual' -- nvarchar(max) END; GO
Running Unit Test for Main Procedure
Run the unit test:
Congratulations, you have successfully unit tested a stored procedure by isolating it from its utility procedure after using spy procedure.
For more information about unit testing please go through the following parts of my previous article about test-driven database development (TDDD):
- Jump to Start Test-Driven Database Development (TDDD) – Part 1
- Jump to Start Test-Driven Database Development (TDDD) – Part 2
- Jump to Start Test-Driven Database Development (TDDD) – Part 3
Things to Do
You can now create database unit tests for slightly complex scenarios where stored procedures call utility procedures.
- Please try changing spy procedure @CommandToExecute argument (value) as @CommandToExecute = ‘set @DayType = ”Nothing” ‘ and see the test is going to fail now
- Please try meeting the business requirement in this article by using test-driven database development (TDDD)
- Please try to meet another business requirement to see the most recent order placed by any customer using test-driven development (TDDD) involving the same utility procedure
- Please try creating a unit test for utility procedure by isolating the main procedure
- Please give yourself a try to create a unit test for a procedure which calls two utility procedures
dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.
- How to Build a Simple Data Warehouse in Azure – Part 1 - September 2, 2021
- SQL Server Business Intelligence – Modern Tools and Technologies - August 9, 2021
- Learn to Use Time Intelligence in Power BI - June 4, 2021