Haroon Ashraf

Three Standard SQL Unit Tests you can write against any Stored Procedure

June 14, 2019 by

This article talks about the three standard SQL unit tests which can be written against any stored procedure ultimately becoming SQL unit testing object to meet internal or external business specification.

This will also shed light on the importance of standardizing your SQL unit testing arsenal to an extent that it becomes quicker to get your SQL unit testing job done against your desired database.

Additionally, this article emphasizes database developers and professionals to think about having stored SQL unit tests templates ready to be used against any existing or upcoming databases.

Pre-requisites

Let us first quickly go through the pre-requisites of the article so that the concepts and walkthroughs in this article can be easily understood by the readers.

T-SQL skills

The article assumes that the readers are well familiar with T-SQL scripting and are comfortable to write and run SQL queries against the databases.

SQL unit testing concepts

It is also desired that the readers have basic SQL unit testing concepts along with the know-how of any of the two database unit testing practices:

  1. Conventional database unit testing
  2. Test-driven database development (TDDD)

To get a quick understanding of these SQL unit testing practices please refer to the following articles:

  1. Conventional SQL Unit Testing with tSQLt in Simple Words
  2. Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing

TSQLt Familiarity

This article also assumes that the readers are well familiar with tSQLt, one of the most famous SQL unit testing frameworks, which by default (design) supports test-driven database development.

If you have not got enough understanding of tSQLt then please refer to the following articles:

  1. tSQLt – A Forgotten Treasure in Database Unit Testing
  2. Why you should cleverly name Database Objects for SQL Unit Testing

Setup Sample Database

Setup a sample database named StandardTSQLT by running the following SQL script:

Run the script and view the sample database and its objects:

Setting up sample database named StandardTSQLT.

Setup tSQLt Framework

Run tSQLt.class.sql (downloaded from tSQLt.org) script against the sample database to add tSQLt framework for SQL unit testing:

Adding tSQLt framework to the sample database.

Create SQL unit test class template

First of all, create a general test class called ObjectTests in the sample database StandardTSQLT as follows:

Please note that this article presents a new approach inspired by the SQL unit testing guidelines by Dave Green.

Why Standard SQL unit tests

It is not odd to think that do we really need standard SQL unit tests while things change frequently in the database and SQL unit testing realm?

Yes, it is good to have standard SQL unit tests so that they can not only save time but also help to standardize our overall SQL database development and testing strategy contributing to streamlining database lifecycle management process.

Typical SQL unit testing scenario

If we closely look at a typical SQL database development and SQL unit testing scenario then we can easily understand the importance of having standard SQL unit tests beforehand as we move from database to database.

A typical SQL unit testing scenario is as follows:

  1. Create a database object to meet some business requirement
  2. Create a SQL unit test to check the database object
  3. Run SQL unit test to check the database object does the job or not
  4. If the test is passed then move on to the next SQL unit test

How many times you have to repeat this process and in fact for one database you may create several database objects which must be unit tested to ensure that they do the job they were created for.

Let us suppose you already have some SQL unit tests in mind or on paper (as a start-up) then would not that be easy to straightaway create or reuse those standard SQL unit tests for any new object you work on.

However, please bear in mind that it is not always possible to have standard SQL unit tests that fit every case.

Stored Procedure and Standard unit tests

Another good example to understand the need for standard SQL unit tests is to look at a stored procedure.

If you reverse engineer a stored procedure it is typically a set of SQL scripts that are frequently used to serve the purpose.

So, if we can wrap a set of repeatable SQL scripts into a stored procedure to avoid repetition and standardize the process then why not we can have standard SQL unit tests to be applicable to most of the SQL unit testing objects.

Let us now focus on the three standard SQL unit tests which you can write against almost any stored procedure.

Three standard unit tests.

1 – Test to check object exists

The first standard SQL unit test against any stored procedure is going to check whether it has been created or not.

Please remember the order in which database object and its SQL unit test is created may vary depending on the SQL unit testing pattern in use.

Conventional SQL unit testing

In this pattern, database objects are created first and their SQL unit tests are written next.

So the order is as follows:

  1. You create a SQL unit testing object which is stored procedure to meet business specification
  2. You create a SQL unit test to check if it exists or not

Obviously, it exists so what is the point of creating the SQL unit test to check object exists or not.

Actually, this is going to make sure the object has not been accidentally or purposely deleted (due to a conflicting requirement). This is somewhat like a schema-bound view which we can call SQL unit test bound object, a new term I am coining for the first time.

Create SQL unit test Object Exists Template

Next, create SQL unit test object exists template as follows:

Dry Run SQL unit test

Test to check object exists has passed.

So, the first most common SQL unit test is to check if the object of interest exists or not is complete now. We have just created this SQL unit test in the form a template that is going to be used afterward.

2 – Test to check object has normal output against normal input data

The next most desired SQL unit test is to check if object outputs normally when given normal input data.

Normal Input Data

Normal input data is any acceptable input given to SQL unit testing object.

For example, if your object under test is a stored procedure which accepts Name parameter then any valid name or string supplied to the stored procedure during the SQL unit test is considered normal input data.

Normal Output Data

This means if an object has been given a normal input we expect it to give us the normal or expected output.

For example if a stored procedure accepts a name and displays the list of authors having the same name then this is normal output data.

Alternatively, if a stored procedure accepts a name and adds it to a table then getting the contents of that table is considered normal output provided it contains the expected data inserted by stored procedure.

In other words, this SQL unit test ensures that the stored procedure (if this is the object under test), when given acceptable input, produces acceptable output.

Create Normal Output When Normal Input Template

Now create this SQL unit test template as follows:

Dry Run SQL unit tests

Test to check object outputs normally when given normal input has passed.

3 – Test to check object has abnormal output against abnormal input data

This is also one of the most widely used SQL unit tests since it checks for abnormal input and expects abnormal output.

Abnormal Input Data

Abnormal input data can be of different forms including no data at all to check the behavior of the object.

Abnormal Output Data

Abnormal output data can also be of various types including no output based on no data.

Please create Abnormal Output against Abnormal Input Template

Yes, I would like you to give it a try and let me know how far you can go. The clue is to consider no input and no output abnormality when writing your SQL unit test template.

Final Word and Further Reading

In this article, we have successfully created two general SQL unit tests templates and purposely left the third one for the readers to try to implement.

However, all of these SQL unit testing templates can now test any general stored procedure.

Please go through the following articles to see these templates in action:

  1. Why you should cleverly name Database Objects for SQL Unit Testing
  2. Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
  3. Conventional SQL Unit Testing with tSQLt in Simple Words

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, 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

700 Views