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

Total: 10 Average: 3.8

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:

Code
-- -- ================================================================

-- Create date: 2018-08-01

-- Setup SQLDevBlogReportTDD database

-- ================================================================




-- Create sample database (SQLDevBlogReportTDD)

CREATE DATABASE SQLDevBlogReportTDD;

GO




-- Use already created SQLDevBlogReportTDD

USE SQLDevBlogReportTDD;




-- (1) Create Article table in the sample database

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.TABLES T

WHERE T.TABLE_NAME = 'Article')

DROP TABLE dbo.Article; -- drop table if already exists




CREATE TABLE Article (

ArticleId INT PRIMARY KEY IDENTITY (1, 1)

,CategoryId INT

,AuthorId INT

,Title VARCHAR(150)

,Published DATETIME2

,Notes VARCHAR(400)

);




-- (2) Create Author table in the sample database

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.TABLES T

WHERE T.TABLE_NAME = 'Author')

DROP TABLE dbo.Author; -- drop table if already exists




CREATE TABLE Author (

AuthorId INT PRIMARY KEY IDENTITY (1, 1)

,Name VARCHAR(40)

,RegistrationDate DATETIME2

,Notes VARCHAR(400)

);




-- (3) Create Article Category table in the sample database

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.TABLES T

WHERE T.TABLE_NAME = 'Category')

DROP TABLE dbo.Category; -- drop table if already exists




CREATE TABLE Category (

CategoryId INT PRIMARY KEY IDENTITY (1, 1)

,Name VARCHAR(50)

,Notes VARCHAR(400)

);




-- (3) Create Article table in the sample database

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.TABLES T

WHERE T.TABLE_NAME = 'Article')

DROP TABLE dbo.Article; -- drop table if already exists




CREATE TABLE Article (

ArticleId INT PRIMARY KEY IDENTITY (1, 1)

,CategoryId INT

,AuthorId INT

,Title VARCHAR(150)

,Published DATETIME2

,Notes VARCHAR(400)

);




-- Adding foreign keys for author and article category

ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId);

ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);




GO




---- (4) Populating Author table

INSERT INTO Author (Name, RegistrationDate, Notes)

VALUES ('Peter', '2017-01-01', 'Database Analyst'), -- 1 (Peter)

('Adil', '2017-01-02', 'Database and Business Intelligence Developer'), -- 2 (Adil)

('Sarah', '2018-01-01', 'Database Analyst Programmer'), -- 3 (Sarah)

('Asim', '2018-01-01', 'Database Analyst'), -- 4 (Asim)

('Sam', '2017-03-01', 'Database Developer'), -- 5 (Sam)

('Akeel', '2017-04-04', 'Database Developer/Tester'), -- 6 (Akeel)

('Mark', '2017-05-05', 'Database Analyst Programmer'),-- 7 (Mark)

('Zara', '2017-06-01', 'Business Intelligence Consultant'); --8 (Zara)

GO

---- (5) Populating Category table

INSERT INTO Category (Name, Notes)

VALUES ('Analysis', 'Database Analysis'), -- 1 (Aanlysis)

('Development', 'Articles about database development'), -- 2 (Development)

('Testing', 'Database testing related articles'), -- 3 (Testing)

('DLM', 'Database lifecycle management'); -- 4 (DLM)




-- (6) Populating Article

INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)

VALUES (1, 1, 'Replicating a problem in SQL', '02-01-2018', ''), -- 1

(1, 2, 'Modern Database Development Tools', '02-01-2018', ''), -- 2

(3, 3, 'Test Driven Database Development (TDDD)', '03-01-2018', ''), -- 3

(3, 1, 'Database Unit Testing Fundamentals', '10-01-2018', ''), -- 4

(3, 3, 'Unit Testing with tSQLt', '10-01-2018', ''), -- 5

(2, 5, 'Developing Databases From Scratch', '2017-03-10', ''), -- 6

(3, 5, 'Advanced Database Unit Testing Frameworks', '2017-03-20', ''), -- 7

(3, 6, 'Automated Database Unit Testing', '2017-05-01', ''), -- 8

(3, 6, 'Database Unit Test using MSSQL Tests', '2017-05-06', ''), -- 9

(1, 6, 'Database Analysis Fundamentals', '2017-05-20', ''), -- 10

(1, 6, 'In-Depth Database Analysis', '2018-01-01', ''), -- 11

(2, 7, 'Automating Database Development Tasks', '2017-06-01', ''), -- 12

(2, 7, 'Database Development with SSDT (SQL Server Data Tools)', '2018-01-01', ''), -- 13

(2, 7, 'Database Development with DbForge Studio for SQL Server', '2018-02-01', ''), -- 14

(3, 7, 'Writing Test Cases for Databases', '2018-02-10', ''), -- 15

(4, 8, 'Understanding DLM (Database Lifecycle Management)', '2017-07-01', ''), -- 16

(4, 8, 'DLM (Database Lifecycle Management) Tips and Tricks', '2017-07-15', ''), -- 16

(1, 8, 'Database Analysis in T-SQL', '2018-01-10', ''), -- 17

(1, 8, 'Advanced Database Analysis Methods', '2018-02-10', ''), -- 18

(3, 8, 'Version Controlling Database Unit Tests', '2018-03-10', ''), -- 19

(3, 8, 'Beyond Database Unit Testing', '2018-06-10', ''); -- 20




-- (7) Create view to see list of authors, articles and categories

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.VIEWS v

WHERE V.TABLE_NAME = 'vwAuthors')

DROP VIEW vwAuthors; -- drop view if already exists

GO




CREATE VIEW dbo.vwAuthors

AS

SELECT

a1.Title AS ArticleTitle

,c.Name AS CategoryName

,a.Name AS AuthorName

FROM Author a

INNER JOIN Article a1

ON a.AuthorId = a1.AuthorId

INNER JOIN Category c

ON a1.CategoryId = c.CategoryId;

GO




-- (8) Create view ArticlesPerAuthor to see total number of articles per author

IF EXISTS (SELECT

*

FROM INFORMATION_SCHEMA.VIEWS v

WHERE V.TABLE_NAME = 'ArticlesPerAuthorReport')

DROP VIEW ArticlesPerAuthorReport; -- drop view if already exists

GO




CREATE VIEW dbo.ArticlesPerAuthorReport

AS

SELECT

a.Name AS [Author]

,COUNT(a1.ArticleId) AS [Total Articles]

FROM Author a

INNER JOIN Article a1

ON a.AuthorId = a1.AuthorId

GROUP BY a.Name;

GO




-- (9)

-- Please install tSQLt Framework to create and run database unit tests




-- (10)

-- Please add and run ArticlesPerAuthorReport unit tests (optional)

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:

 

CREATE PROCEDURE ArticlesPerAuthorByYearReport.[test to check ArticlesPerAuthorByYearReport exists]

AS

BEGIN

--Assemble

--Act

--Assert

EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorByYearReport'

,@Message = N''




END;

GO

 

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:

 

-- Create report object (stored procedure) stub

CREATE PROCEDURE dbo.ArticlesPerAuthorByYearReport

@Year INT

AS

SELECT 'Adil' AS Author, 10 AS [Total Articles], 0000 AS [Year]

UNION ALL

SELECT 'Sam' AS Author, 5 AS [Total Articles], 0000 AS [Year]

GO

 

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:

 

CREATE PROCEDURE ArticlesPerAuthorByYearReport. Setup

AS

BEGIN

--Assemble

-- Create mocked up tables (blank copies of original tables without constraints and data)

EXEC tSQLt.FakeTable @TableName = N'Author'

,@SchemaName = N'dbo'




EXEC tSQLt.FakeTable @TableName = N'Article'

,@SchemaName = N'dbo'




EXEC tSQLt.FakeTable @TableName = N'Category'

,@SchemaName = N'dbo'




-- Add rows to the mocked up tables

INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)

VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),

(2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')

INSERT INTO Category (CategoryID,Name, Notes)

VALUES (1,'Database Development', '-'),

(2,'Business Intelligene','-');




INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)

VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),

(1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),

(1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),

(1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2016,02,01),'10K Views'),

(1,2, 2, 'Tabular Models', DATEFROMPARTS(2016,02,01),'50K Views')




-- Create expected table

CREATE TABLE ArticlesPerAuthorByYearReport.Expected

(Author VARCHAR(40),[Total Articles] INT,[Year] INT)




-- Create actual table

CREATE TABLE ArticlesPerAuthorByYearReport.Actual

(Author VARCHAR(40),[Total Articles] INT,[Year] INT)







-- Add expected results into expected table for the year 2017

INSERT INTO ArticlesPerAuthorByYearReport.Expected (Author, [Total Articles],[Year])

VALUES ('Zak', 3,2017)




END;

GO

 

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

 

-- Create unit test to check ArticlesPerAuthorByYearReport outputs correct data

CREATE PROCEDURE ArticlesPerAuthorByYearReport.[test to check ArticlesPerAuthorByYearReport outputs correct data]

AS

BEGIN

--Assemble (Test Code written in Setup Procedure)

-- Create mocked up tables (blank copies of original tables without constraints and data)

-- Add rows to the mocked up tables

-- Create expected table

-- Create actual table

-- Add expected results into expected table

--Act

-- Call desired object (stored procedure) and put results into an actual table

INSERT INTO ArticlesPerAuthorByYearReport.Actual

EXEC dbo.ArticlesPerAuthorByYearReport @Year=2017




--Assert

-- Compare the expected and actual tables

EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorByYearReport.Expected'

,@Actual = N'ArticlesPerAuthorByYearReport.Actual'

END;

GO

 

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:

 

-- Create report object (stored procedure) to show articles per author for a specified year

CREATE PROCEDURE dbo.ArticlesPerAuthorByYearReport

@Year INT

AS




SELECT

a.Name AS [Author],COUNT(a1.ArticleId) AS [Total Articles],YEAR(a.RegistrationDate) AS [Year]

FROM Author a

INNER JOIN Article a1

ON a.AuthorId = a1.AuthorId

WHERE YEAR(a.RegistrationDate) = @Year

GROUP BY a.Name,YEAR(a.RegistrationDate)

GO

 

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 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).