Unit Testing Report Procedures – Jump to Start TDDD Part-4

This article is a walk-through of creating a stored procedure through test-driven database development (TDDD) in order to meet a reporting requirement that cannot be fulfilled by using a database view.

This article also provides some useful hints about cases of preferring stored procedure over database view as a potential object that is going to fulfill the business requirement(s).

Test-driven database development (TDDD) Recap

Test-driven database development (commonly referred as TDDD) is an approach where unit testing triggers the database development process in the following way: the responsibility for meeting the business requirement(s) potential database object(s) must pass the unit test(s) that satisfy the following conditions under normal circumstances:

  1. The database object must exist
  2. The database object must function properly to meet the business requirement only

The unit tests have to fail first before getting passed because they are created earlier than the object and the same is true for the object functionality unit test.

Jump to Start TDDD Part-1 Recap

In the first part of the article, traditional database development was compared with test-driven database development (TDDD) along with coding examples. TDDD began with the unit-test to check if the object exists and followed by the unit test to check that object is meeting the requirement.

For a detailed walk-through, please, refer to the first part of this article.

Jump to Start TDDD Part-2 Recap

The second part of the article was focused on meeting a reporting requirement to view articles per author. This requirement was finally met by the unit test to check if the object is created and followed by the unit test to check if the object sends the correct number of articles per author or not. We ran the database unit tests that passed to satisfy the reporting requirement.

Please refer to the second part of this article to the see details of all the steps for meeting the reporting requirement.

Jump to Start TDDD Part-3 Recap

In the third part of the article, we designed the data report that was based on the database object (view) in order to meet the business requirement. The data report shows the number of articles per author as per business requirement that internally calls the view which was created through TDDD.

For detailed steps about the above-mentioned things, please refer to the third part of this article.

Importance of Database Object in TDDD

The potential database object to meet the business requirement(s) has the highest importance when it comes to TDDD under normal circumstances.

If you are unsure that the functionality of the database object can meet the business requirement then it is not worth to proceed further with it.

TDDD begins with thinking of a potential database object that is capable of meeting the requirement.

So far in the previous parts of the article, we have used database view which fulfills the reporting requirement(s) but it is not always the case.

The developer has to choose between view and stored procedure depending on the business requirements.

Use of Database View

In case the business requires to see “Total number of articles per author report” (business requirement), it can be fulfilled by using database view that contains the desired aggregation (sum, average etc.).

This requirement solely depends on articles and authors stored in the database and do not require any input by the end-user, so database view is the best fit.

Use of Stored Procedure

In case the business users are interested to know “Total number of articles per author for a specified year”, the database view is not capable of meeting the requirement because it is not defined at the time of writing of the script (exactly the year is going to be desired by the business users).

This requires a database object with parameter(s) capability and it is exactly the stored procedure.

This is illustrated as follows:


TDDD4 pic1


Satisfying Stored Procedure Based Reporting Requirement

Consider a new business requirement in order to create the report that shows “Total number of articles per author for a specified year”.

Setup Sample Database (SQLDevBlogReportTDD)

If you have already created and populated the sample database (SQLDevBlogReportTDD) by using part-3 of this tip, then please skip this section. Otherwise, please follow the steps below:

Create and populate the sample database called “SQLDevBlogReportTDD” as follows:


Run the view “ArticlesPerAuthorReportthat we have just created in order to see the results:


TDDD4 pic2


Please note that I am using dbForge Studio for SQL Server in this article, so output look may differ if you run the same code in SSMS (SQL Server Management Studio). But it does not matter as far as scripts and their results are concerned.

Select Database Object (AuthorsPerArticleByYearReport)

Name the potential database object as “AuthorsPerArticleForYearReport”.

As we mentioned above, the database view is sufficient to meet the reporting requirement despite the absence of the “specified year”. But this variable means that we need the stored procedure which will pass “year” as an argument in order to run the report for showing the desired results.

Write Object Exists Unit Test

As I mentioned in the previous parts of this article, we begin with writing the basic unit test in order to check the existence or absence of the desired object.

This tip works for dbForge Studio for SQL Server (or only dbForge Unit Test) and SSMS (SQL Server Management Studio). However, please note that, while using SSMS (SQL Server Management Studio), I assume you have already installed the tSQLt Framework and ready to write the unit tests.

To create the first database unit test, right-click the “SQLDevBlogReport” database. On the shortcut menu, click “Unit Test” and then “Add New Test”…


TDDD4 pic3


Write the following test code:



Run Object Exists Unit Test (Fail First)

Right click on the database (if you are using dbForge Studio for SQL Server or dbForge Unit Test) and click “View Test List” under “Unit Test” to see Test List Manager:


TDDD4 pic4


Check the “ArticlesPerAuthorByYearReport” test class and click the run test icon:


TDDD4 pic5


This is to comply with TDDD where the unit test checking if object existence is written before the object is even created. So we expect the test to fail first.

Create Object Stub (dummy object)

In the next step, we are going to create an object stub that mocks the object functionality. But at this point, we are interested only in creating the object so putting the desired functionality is out of the question at the moment.

Create a stored procedure type object as the stub and call it “ArticlesPerAuthorByYearReportby using the following code:



Re-run Object Exists Unit Test

We created the object stub which is sufficient for the basic unit test that checks for the existence of the object to pass:


TDDD4 pic6


Write Object Functionality Unit Test

To comply with TDDD, we need to write a unit test to check whether the desired object “ArticlesPerAuthorByYearReport” functions properly or not.

Since the object was created as a stub (placeholder), this unit test is also going to fail first. This keeps us to be focused on the point that the object has to function properly yet despite the fact it was created and passed the basic check of its existence.

This is illustrated as follows:


TDDD4 pic7


Create a second unit test to check if object outputs correct data by creating a setup procedure (which helps us to write shared test code within the same test class) that is followed by the unit test:



Write the unit test to check if object functions properly or not:



Run Object Functionality Test (Fail First)

Running the unit test of checking the proper work of the object should fail now since we have not added the desired functionality to the object yet:


TDDD4 pic8


Add Object Functionality

Add the object functionality by modifying the stored procedure as follows:



Please note that if you are using declarative database development tool like dbForge Studio for SQL Server then you will use “Create Procedure” statement to modify the object. For tools like SSMS (SQL Server Management Studio) you must use “ALTER Procedure”.

Rerun Object Functionality Unit Test

Rerunning the database unit test for checking the proper object functioning gives us the following results:


TDDD4 pic9


Congratulations! You have successfully unit tested the reporting procedure that is responsible for meeting the business requirement.

Things to Do

You can now create a database report based on the object whether database view or stored procedure is created through test-driven development.

  1. Please try to meet another reporting requirement to show articles for a specified author by using TDDD.
  2. Please try meeting another reporting requirement to show articles for a specified category that is following test-driven database development.
  3. While using TDDD, please, try to meet a reporting requirement to view the authors that have not written an article.
  4. Please try meeting a business requirement to search article by title by using TDDD.


Useful tool:

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

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
Haroon Ashraf