Haroon Ashraf
SQL unit testing GetWeeklyReportStartEndDate has passed

Creating SQL Unit Testing Utility Procedures with tSQLt

September 10, 2019 by

This article gives hands-on experience of writing basic utility procedures and creating their SQL unit tests using tSQLt an advanced SQL unit testing framework.

The article also highlights the importance and application of utility procedures in day to day database report writing tasks.

Additionally, the readers of this article are going to learn some tips to meet common reporting requirements with the help of SQL utility procedures.

There is a hidden challenge in this article as well for those SQL programming enthusiasts who would like to go beyond the basics to test their skills and learn more in this effort.

About SQL Utility procedures

SQL utility procedures generally fall into two categories:

  1. System utility procedures
  2. User-defined utility procedures

System utility procedures

System utility procedures are out of the box SQL procedures available with SQL Server which help in maintaining the good health of your system (SQL Server) and the entities (databases, SSIS Packages, etc.) of your system alongside providing meta-data (data about databases) to facilitate tracking, logging and error resolving.

Example

Let us take an example of sp_databases system stored procedure which provides a list of all the databases belonging to SQL server instance or accessible via gateway according to Microsoft documentation.

The procedure returns the list of all the databases as follows:

System stored procedure to show list of all the databases

A polite reminder for the beginners that the output may vary based on the databases created in the SQL server instance you are running this stored procedure against.

Also, SQL unit testing a system utility procedure is not recommended at all since they already undergo a lot of testing before the product (Microsoft SQL Server) gets shipped.

User – defined utility procedure

The user-written stored procedures to act as a utility are known as user defined utility procedures.

In other words, any stored procedure written to facilitate another stored procedure can be called a utility procedure.

By utility procedures we mean the stored procedures which are specifically designed to facilitate business or system requirements generally by performing some functionality and handing over their output to another database object which then plays a major role in the overall database (requirement) picture.

Example

A good example is of creating a utility procedure to implement user-defined database logging process (as per requirements) which saves the details of the database user who adds a new record to the table.

Now the first procedure AddData inserts the data into the table while you also have to write a general LogInfo utility procedure which is going to insert the information of the user and the time when AddData procedure was called to insert new record.

Creating Utility Procedure

In this section we are going to write a utility procedure based on business requirements.

Please remember to think about the utility procedure from the SQL unit testing perspective as well as we do in test-driven database development mentioned earlier.

Business requirement

A database report is desired to show complete weekly sales.

Preliminary Analysis

The database report to show weekly sales must require a utility procedure to output the start and end date for a weekly report based on the dynamic nature of the report.

For example, if today is 25 July 2019 then the weekly report must show sales figures from 15 July 2019 up until 21 July 2019 because this is the most recent complete week and this is how professional reports are written and managed.

Last complete week for weekly report

Planning utility procedure logic

It is worth doing some mental exercise to define the logic of the utility procedure which is going to give us start and end date of the weekly sales report.

The DateAdd() SQL function is highly recommended in this scenario which we are going to use to build this utility procedure.

Test-driven database development

I highly recommend at this point to use a test-driven database development method to create your utility procedure which means your SQL unit testing is going to derive your database object definition.

Please refer to the article Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing to get more information about it.

Since the focus of this article is not implementing test-driven database development so we are creating the utility procedure first bypassing test-driven database development.

Creating a Sample Database

Let us create and populate a sample database named ITSalvesV2 using the following T-SQL script:

List of week day name and number

As discussed earlier the utility procedure must make use of SQL built-in date time function to calculate the start and end of the last week.

Here, again SQL unit testing of the built-in date time function is not required unless you are using a user-defined function then its SQL unit testing must be planned but isolating it from other things.

Before we write utility procedure let us understand how the day of the week is represented in number according to the current date time settings in SQL Server.

Running the above script shows us the following output:

List of name and number of the week day

Well, the utility procedure we are going to write to get last week’s start and end date does not depend on the above information much but I am leaving a clue for the readers here to craft their own stored procedure based on the above logic.

Writing Utility Procedure

We have to now write utility procedure in such a way that we should be able to assign weekly report start date as Last week’s Monday and end date as Last week’s Sunday

Test running utility procedure

After creating the utility procedure in the sample database we need to test run by running the following T-SQL script:

The output is as follows:

Getting last complete week before 17 July 2019

Use of utility procedure in report main procedure

Let us now quickly analyze how the utility procedure is used by the report procedure. The utility procedure we have created earlier can be used with any weekly report procedure.

Creating WeeklySalesReport procedure

If we are to create a WeeklySalesReport procedure then the utility procedure GetWeeklyReportStartEndDate is going to return the start and end date of the last complete week and we can base our sales on these returned start and end dates (of the last complete week).

The stored procedure to show weekly sales can be written as follows:

SQL Unit testing utility procedure

Let us try to understand the answers to the following questions:

How the utility procedure called within the main report procedure is unit tested?

A utility procedure is just like any other stored procedure and its SQL unit testing is done in the same way as other procedures are unit tested.

Which procedure out of the two should be unit tested first?

Although the order does not matter much but the way the main procedure is unit tested is going to be slightly different than the SQL unit testing utility procedure.

When we write the SQL unit test for the main procedure we have to use utility procedure as a stub rather than actual procedure while there must be another SQL unit test to ensure that the utility procedure is working well and both SQL unit tests must pass.

Setup tSQLt framework

We assume that tSQLt has already been installed by downloading tSQLt and running tSQLt.class.sql script and ready to be used against the sample database ITSalesV2.

Please refer to the article Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing to get more information on how to setup the tSQLt unit testing framework.

Create SQL unit test class for utility procedure

Before we begin SQL unit testing the database object (utility procedure) we need to create a general test class called GetWeeklyReportStartEndDateTests in the sample database ITSalesV2 as follows:

TSQLt Test Run

Let us dry run SQL unit tests:

SQL unit testing dry run using tSQLt

Write utility procedure GetWeeklyReportStartEndDate SQL unit test

Let us now write the SQL unit test for the utility procedure as follows:

Run tSQLt Tests

Now run the tSQLt tests to see the results:

SQL unit testing GetWeeklyReportStartEndDate has passed

Congratulations, you have successfully completed the task of SQL unit testing the utility procedure which is now ready to be utilized by professional weekly reports to serve client business needs!

Table of contents

tSQLt – A Forgotten Treasure in Database Unit Testing
Conventional SQL Unit Testing with tSQLt in Simple Words
Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
10 Most Common SQL Unit Testing Mistakes
Why you should cleverly name Database Objects for SQL Unit Testing
Three Standard SQL Unit Tests you can write against any Stored Procedure
Creating SQL Unit Testing Utility Procedures with tSQLt

Haroon Ashraf

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 holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.

He began his professional life as a computer programmer more than a decade 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).

He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.

View all posts by Haroon Ashraf
Haroon Ashraf
SQL unit testing

About 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 holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.He began his professional life as a computer programmer more than a decade 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).He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.View all posts by Haroon Ashraf

323 Views