Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure

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:

EXEC sys.sp_server_info

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:

1. Isolating from utility procedure when unit testing the main procedure.
2. 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:

1. Sample Database
2. 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)

Things to Do

You can now create database unit tests for slightly complex scenarios where stored procedures call utility procedures.

1. Please try changing spy procedure @CommandToExecute argument (value) as @CommandToExecute = ‘set @DayType = ”Nothing” ‘ and see the test is going to fail now
2. Please try meeting the business requirement in this article by using test-driven database development (TDDD)
3. 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
4. Please try creating a unit test for utility procedure by isolating the main procedure
5. Please give yourself a try to create a unit test for a procedure which calls two utility procedures

Useful tool:

dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.