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:
- System utility procedures
- 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.
|
1 2 |
-- System stored procedure to show list of all the databases EXEC sp_databases |
The procedure returns the list of all the databases as follows:
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE DATABASE ITSalesV2; GO USE ITSalesV2 GO -- (1) Create DailySales table CREATE TABLE [dbo].[DailySale] ( [SaleId] INT IDENTITY (1, 1) NOT NULL, [SellingDate] DATETIME2 (7) NULL, [Customer] VARCHAR (50) NULL, [Product] VARCHAR (150) NULL, [TotalPrice] DECIMAL (10, 2) NULL, CONSTRAINT [PK_DailySale] PRIMARY KEY ([SaleId]) ); -- (2) Insert data SET IDENTITY_INSERT [dbo].[DailySale] ON INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (1, N'2019-07-15 00:00:00', N'Asif', N'Dell Laptop', CAST(300.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (2, N'2019-07-15 00:00:00', N'Mike', N'Dell Laptop', CAST(300.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (3, N'2019-07-16 00:00:00', N'Adil', N'Lenovo Laptop', CAST(350.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (4, N'2019-07-17 00:00:00', N'Sarah', N'HP Laptop', CAST(250.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (5, N'2019-07-17 00:00:00', N'Asif', N'Dell Desktop', CAST(200.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (6, N'2019-07-20 00:00:00', N'Sam', N'HP Desktop', CAST(300.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (7, N'2019-07-20 00:00:00', N'Mike', N'iPad', CAST(250.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (8, N'2019-07-21 00:00:00', N'Mike', N'iPad', CAST(250.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (9, N'2019-07-22 00:00:00', N'Peter', N'Dell Laptop', CAST(350.00 AS Decimal(10, 2))) INSERT INTO [dbo].[DailySale] ([SaleId], [SellingDate], [Customer], [Product], [TotalPrice]) VALUES (10, N'2019-07-23 00:00:00', N'Peter', N'Asus Laptop', CAST(400.00 AS Decimal(10, 2))) SET IDENTITY_INSERT [dbo].[DailySale] OFF |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- List of name and number of the week day DECLARE @DateTable TABLE (NameOfTheDay VARCHAR(30), NumberOfTheDay SMALLINT) DECLARE @Date DATETIME SET @Date='01 Apr 2019' WHILE @DATE<'08 Apr 2019' BEGIN INSERT INTO @DateTable SELECT (DATENAME(WEEKDAY,@Date)),(DATEPART(WEEKDAY,@DATE)) SET @Date=@Date+1 END SELECT * FROM @DateTable |
Running the above script shows us the following output:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- The utility procedure to calculate the last week's start and end date CREATE PROC GetWeeklyReportStartEndDate @CurrentDate DATETIME2 ,@LastWeekStartDate DATETIME2 OUTPUT ,@LastWeekEndDate DATETIME2 OUTPUT AS BEGIN SET NOCOUNT ON SET @LastWeekStartDate=DATEADD(DD,-7,@CurrentDate) -- Go back 7 days to get last week SELECT @LastWeekStartDate= CASE DATENAME(DW,@LastWeekStartDate) WHEN 'Monday' THEN @LastWeekStartDate -- If last week start is Monday then OK WHEN 'Tuesday' THEN DATEADD(DD,-1,@LastWeekStartDate) -- If Tuesday go back 1 day to start from Monday WHEN 'Wednesday' THEN DATEADD(DD,-2,@LastWeekStartDate) -- If Wednesday go back 2 days to start from Monday WHEN 'Thursday' THEN DATEADD(DD,-3,@LastWeekStartDate) -- If Thursday go back 3 days to start from Monday WHEN 'Friday' THEN DATEADD(DD,-4,@LastWeekStartDate) -- If Friday go back 4 days to start from Monday WHEN 'Saturday' THEN DATEADD(DD,-5,@LastWeekStartDate) -- If Saturday go back 5 days to start from Monday WHEN 'Sunday' THEN DATEADD(DD,-6,@LastWeekStartDate) -- If Sunday go back 5 days to start from Monday END ,@LastWeekEndDate=(DATEADD(DD,6,@LastWeekStartDate)) -- Move forward 6 days to get last weekend date END |
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:
|
1 2 3 4 5 6 7 8 9 |
--(1) Call the utility procedure to get last week start and end date DECLARE @StartDate DATETIME2, @EndDate DATETIME2,@TheDate DATETIME2 SET @TheDate='17 Jul 2019' EXEC GetWeeklyReportStartEndDate @CurrentDate=@TheDate ,@LastWeekStartDate=@StartDate OUTPUT, @LastWeekEndDate=@EndDate OUTPUT --(2) Show current date along with last week start date and end date SELECT Format(@TheDate,'dd-MMM-yyyy') as CurrentDate,Format(@StartDate,'dd-MMM-yyyy') AS WeekStartDate,Format(@EndDate,'dd-MMM-yyyy') as WeekEndDate |
The output is as follows:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- The procedure to show weekly sales report CREATE PROCEDURE [dbo].[ShowWeeklySales] AS DECLARE @StartDate DATETIME2, @EndDate DATETIME2,@TheDate DATETIME2,@AfterEndDate DATETIME2 SET @TheDate=GETDATE() -- get today's date EXEC GetWeeklyReportStartEndDate @CurrentDate=@TheDate ,@LastWeekStartDate=@StartDate OUTPUT, @LastWeekEndDate=@EndDate OUTPUT SET @AfterEndDate=DATEADD(DD,1,@EndDate) SELECT * FROM dbo.DailySale S WHERE S.SellingDate>=@StartDate and S.SellingDate<@AfterEndDate RETURN 0 |
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:
|
1 2 3 4 5 6 7 8 |
USE ITSalesV2 GO -- Creating unit test class GetWeeklyReportStartEndDateTests CREATE SCHEMA [GetWeeklyReportStartEndDateTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='GetWeeklyReportStartEndDateTests' |
TSQLt Test Run
Let us dry run SQL unit tests:
|
1 2 |
-- Dry run sql unit tests EXEC tsqlt.RunAll |
Write utility procedure GetWeeklyReportStartEndDate SQL unit test
Let us now write the SQL unit test for the utility procedure as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
--Create unit test to check object normal output for normal input template CREATE PROCEDURE [GetWeeklyReportStartEndDateTests].[test to check object outputs normally when given normal input] AS -- Assemble CREATE TABLE GetWeeklyReportStartEndDateTests.expected ( -- Creat expected table CurrentDate DATETIME2, StartDate DATETIME2, EndDate DATETIME2 ); INSERT INTO GetWeeklyReportStartEndDateTests.expected -- Insert data into exepcted table (CurrentDate, StartDate, EndDate) VALUES ('14 May 2019','06 May 2019','12 May 2019') CREATE TABLE GetWeeklyReportStartEndDateTests.actual ( -- Creat actual table CurrentDate DATETIME2, StartDate DATETIME2, EndDate DATETIME2 ); DECLARE @TheDATE DATETIME2 ='14 May 2019',@StartDate DATETIME2, @EndDate DATETIME2 -- Act EXEC GetWeeklyReportStartEndDate @CurrentDate=@TheDate -- call the utility procedure ,@LastWeekStartDate=@StartDate OUTPUT, @LastWeekEndDate=@EndDate OUTPUT INSERT INTO GetWeeklyReportStartEndDateTests.actual -- put results into actual table SELECT @TheDATE,@StartDate,@EndDate -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='GetWeeklyReportStartEndDateTests.Expected',@Actual='GetWeeklyReportStartEndDateTests.Actual' |
Run tSQLt Tests
Now run the tSQLt tests to see the results:
|
1 2 |
-- Run tsqls unit tests EXEC tsqlt.RunAll |
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
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
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023