SQL Unit Testing Stored Procedure with dbForge Unit Test for SQL Server

Unit testing is an essential component of the database DevOps process. Its primary goal is to test the constituent parts of the database objects in order to identify any malfunctions or flaws early in the project. This approach allows database developers to ensure the changes they have made are verified, and the project will work properly.

In our article, we will mainly focus on unit testing a stored procedure in a SQL Server database and exemplify how simple it is to conduct unit tests with the dbForge Unit Test tool.

Previously, we discussed the process of creating a SQL Server database for a recruitment service.

Img.1. The database schema for a recruitment service

As shown above, the database contains the following entities:

• Employee
• Company
• Position
• Project
• Skill

Nonetheless, in the series of articles, we somehow overlooked a crucial aspect of unit testing. So now, I suggest we have a closer look at this method and exemplify it by implementing the SearchEmployee stored procedure for an employee search based on certain skills. To ensure data integrity, we should add a unique constraint on the Skill table as follows:

ALTER TABLE [dbo].[Skill] ADD CONSTRAINT UniqueSkillName UNIQUE (SkillName);

However, before doing that, make sure the data in the SkillName field doesn’t contain any duplicate entries using the following query:

SELECT
	[SkillName]
FROM [JobEmpl].[dbo].[Skill]
GROUP BY [SkillName]
HAVING COUNT(*) > 1;

Supposing you have duplicate entries, you will need to normalize all records to unique values for the SkillName field relative to one another.

Okay, one point completed: we have created a uniqueness constraint in the names of the skills.

Now, it’s time to implement the SearchEmployee stored procedure, as shown below:

CREATE PROCEDURE [dbo].[SearchEmployee]
@SkillList NVARCHAR(MAX),
@CountNotSkill INT = 1
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @count_skills INT;

	SELECT
		[value] INTO #tbl_skill_tmp
	FROM STRING_SPLIT(@SkillList, N';');

	SELECT
		s.[SkillID]
	   ,s.[SkillName] INTO #tbl_skill
	FROM #tbl_skill_tmp AS tt
	INNER JOIN [dbo].[Skill] AS s
		ON s.[SkillName] = tt.[value];

	SET @count_skills = (SELECT
			COUNT(*)
		FROM #tbl_skill);

	SELECT
		jh.*
	   ,p.[ProjectName]
	   ,p.[Description] AS [ProjectDescription]
	   ,ts.* INTO #tbl_res0
	FROM [dbo].[JobHistory] AS jh
	INNER JOIN [dbo].[Project] AS p
		ON p.[ProjectID] = jh.[ProjectID]
	INNER JOIN [dbo].[ProjectSkill] AS ps
		ON ps.[ProjectID] = p.[ProjectID]
	INNER JOIN #tbl_skill AS ts
		ON ps.[SkillID] = ts.[SkillID];

	SELECT
		[EmployeeID]
	   ,[SkillID]
	   ,MIN([SkillName]) AS [SkillName]
	   ,SUM(DATEDIFF(DAY, [StartDate], COALESCE([FinishDate], GETDATE()))) AS [Days]
	   ,MIN([StartDate]) AS [StartDate]
	   ,MAX(COALESCE([FinishDate], GETDATE())) AS [FinishDate] INTO #tbl_res
	FROM #tbl_res0
	GROUP BY [SkillID]
			,[EmployeeID];

	SELECT
		emp.[EmployeeID]
	   ,emp.[LastName]
	   ,emp.[FirstName]
	   ,r.[SkillID]
	   ,r.[SkillName]
	   ,r.[StartDate]
	   ,r.[FinishDate]
	   ,r.[Days] / 365 AS [Years]
	   ,(r.[Days] - (r.[Days] / 365) * 365) / 30 AS [Months]
	   ,r.[Days] - (r.[Days] / 365) * 365 - ((r.[Days] - (r.[Days] / 365) * 365) / 30) * 30 AS [Days] INTO #tbl_res2
	FROM #tbl_res AS r
	INNER JOIN [dbo].[Employee] AS emp
		ON emp.[EmployeeID] = r.[EmployeeID];

	SELECT
		[EmployeeID]
	   ,[LastName]
	   ,[FirstName] INTO #tbl_empl
	FROM #tbl_res2;

	SELECT
		ts.[SkillID]
	   ,te.[EmployeeID]
	   ,ts.[SkillName]
	   ,te.[LastName]
	   ,te.[FirstName] INTO #tbl_skill_empl
	FROM #tbl_skill AS ts
	CROSS JOIN #tbl_empl AS te;

	SELECT
		tse.[EmployeeID]
	   ,tse.[LastName]
	   ,tse.[FirstName]
	   ,tse.[SkillID]
	   ,tse.[SkillName]
	   ,tr2.[StartDate]
	   ,tr2.[FinishDate]
	   ,tr2.[Years]
	   ,tr2.[Months]
	   ,tr2.[Days] INTO #tbl_res3
	FROM #tbl_skill_empl AS tse
	LEFT OUTER JOIN #tbl_res2 AS tr2
		ON tse.[SkillID] = tr2.[SkillID]
			AND tse.[EmployeeID] = tr2.[EmployeeID];

	SELECT
		[EmployeeID] INTO #tbl_empl_res
	FROM (SELECT
			[EmployeeID]
		   ,[SkillID]
		FROM #tbl_res3
		WHERE [Months] >= 6 OR [Years]>=1
		GROUP BY [EmployeeID]
				,[SkillID]) AS t
	GROUP BY [EmployeeID]
	HAVING COUNT(*) >= @count_skills - @CountNotSkill;

	SELECT
		tr2.[EmployeeID],
		tr2.[LastName],
		tr2.[FirstName],
		tr2.[SkillID],
		tr2.[SkillName],
		tr2.[StartDate],
		tr2.[FinishDate],
		tr2.[Years],
		tr2.[Months],
		tr2.[Days]
	FROM #tbl_empl_res AS ter
	INNER JOIN #tbl_res2 AS tr2
		ON ter.[EmployeeID] = tr2.[EmployeeID];

	SELECT
		tr2.[EmployeeID],
		tr2.[LastName],
	    tr2.[FirstName],
	    tr0.[CompanyID],
		(SELECT TOP(1) com.[CompanyName] FROM [dbo].[Company] AS com WHERE com.[CompanyID]=tr0.[CompanyID]) AS [CompanyName],
		tr0.[PositionID],
		(SELECT TOP(1) p.[PositionName] FROM [dbo].[Position] AS p WHERE p.[PositionID]=tr0.[PositionID]) AS [PositionName],
		tr0.[ProjectID],
		tr0.[StartDate],
		tr0.[FinishDate],
		tr0.[Description],
		tr0.[ProjectName],
		tr0.[ProjectDescription],
		tr0.[SkillID],
		tr0.[SkillName],
		tr0.[Achievements],
		tr0.[ReasonsForLeavingTheProject],
		tr0.[ReasonsForLeavingTheCompany]
	FROM #tbl_res2 AS tr2
	INNER JOIN #tbl_res0 AS tr0
		ON tr0.[EmployeeID] = tr2.[EmployeeID]
	INNER JOIN #tbl_skill AS ts
		ON ts.[SkillID] = tr0.[SkillID];

	DROP TABLE #tbl_skill_tmp;
	DROP TABLE #tbl_skill;
	DROP TABLE #tbl_res;
	DROP TABLE #tbl_res2;
	DROP TABLE #tbl_empl;
	DROP TABLE #tbl_skill_empl;
	DROP TABLE #tbl_res3;
	DROP TABLE #tbl_empl_res;
	DROP TABLE #tbl_res0;
END
GO

Why not examine the work of the SearchEmployee stored procedure in greater detail?

For starters, it has two input parameters:

1. @SkillList is the list of skills, separated by semicolons.
2. @CountNotSkill indicates the number of skills that can be absent (1 by default).

Let’s now move on to the body of the SearchEmployee stored procedure:

1. First, we define the variable @count_skills that is used to count the number of skills found in the database that correspond to the reported number in the input parameter @SkillList.
2. Next, the @SkillList string is transformed into the temporary table #tbl_skill_tmp with the built-in function STRING_SPLIT.
3. Then, all the suitable skills from the Skill table are found and placed into a new temporary table named #tbl_skill_tmp.
4. After that, @count skills is counted in accordance with par.1.
5. Following that, the necessary information on the project (Project table) and the job history (JobHistory table) is collected based on the set skills; the result goes to a temporary table called #tbl_skill_tmp.
6. Next, the information obtained in par. 5 is grouped according to the identifiers of skill and employee, and the result goes to the temporary table #tbl_res.
7. Further on, the information obtained in par. 6 is combined with the Employee table so as to get the employees’ details (first and last name), and the result goes to the temporary table #tbl_res2. The query also counts how long each skill has been applied in years, months, and days to make the subsequent analysis more convenient.
8. After that, the information on the employees is retrieved from the result in par.7, and the final result is placed into the temporary table #tbl_empl.
9. Then, the Cartesian product of the tables #tbl_skill and #tbl_empl is made, and the result is put into the temporary table #tbl_skill_empl.
10. Next, a temporary table named #tbl_res3 is created, it includes the product of two temporary tables #tbl_skill_empl and #tbl_res2 where each pair employee and skill has matching information obtained in par.7.
11. Then, the employee identifiers that comply with the input parameters are collected into the temporary table #tbl_empl_res. At this, the skill is considered to be valid if it has been used for at least 6 months.
12. Next, follows the resulting output of employees and their skills with the time of their use measured in years, months, and days, as well as the start and end date of their application.
13. You will then see a detailed summary of the employee’s history regarding the skills we are interested in.
14. In the end, we drop all the temporary tables created in this stored procedure.

After the above-mentioned steps, we can extract the names of the employees who are competent in C# and T-SQL languages as well as the ASP.NET technology on the condition that there can be maximum one lacking skill, as shown below:

EXEC [dbo].[SearchEmployee] @SkillList = N'C#;T-SQL;ASP.NET'
						   ,@CountNotSkill = 1;

Create and run a SQL Server Unit Test of the stored procedure

To simplify unit testing, this stored procedure should have been divided into two: the first one to output the main information and the second one to output the details of the found employees’ history. Yet, for ease of understanding, we are going to show how to apply unit-testing for the stored procedure.

As for the tool we are going to apply: it is the Unit Test tool, which is built-in SSMS and is also a part of the dbForge Studio for SQL Server.

Let’s have a look at the tool’s functionality in SSMS.

Right-click on the JobEmpl database and in the drop-down list, select Unit Test\”Install Test Framework…”:

Img.2. Starting the installation of Test Framework

Similarly, the menu can be called by right-clicking any node inside the JobEmpl database.

Following that, make sure the server and the database are correct and click “Install”:

Img.3. Installing Test Framework

The process of installation goes as follows:

Img.4. The Test Framework installation process

At the end of the installation process, you will receive a completion message. Click the “Finish” button:

Img.5. The successful completion of the Test Framework installation

Take notice of the JobEmpl database. Below, you can see that the tSQLt objects (tables, views, functions, and stored procedures) have been created:

Img.5-1. Created tSQLt objects (part 1)

Img.5-2. Created tSQLt objects (part 2)

Next, to create a test, right-click on the JobEmpl database and select the Unit Test\” Add New Test…” command:

Img.6. The creation of a new unit test

We now need to customize the settings for the new test named UT_SearchEmployee_Exception and click “Add Test”:

Img.7. Customizing the settings for the created test

At the end of the test creation, an information window appears. Click the “Finish” button:

Img.8. The successful completion of test creation

You will then see a new tab in SSMS with the following code:

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE CL1.[test UT_SearchEmployee_Exception]
AS
BEGIN
  --Assemble
  --  This section is for code that sets up the environment. It often
  --  contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure
  --  along with INSERTs of relevant data.
  --  For more information, see http://tsqlt.org/user-guide/isolating-dependencies/

  --Act
  --  Execute the code under test like a stored procedure, function or view
  --  and capture the results in variables or tables.

  --Assert
  --  Compare the expected and actual values, or call tSQLt.Fail in an IF statement.
  --  Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
  --  For a complete list, see: http://tsqlt.org/user-guide/assertions/
  EXEC tSQLt.Fail 'TODO:Implement this test.'

END;

Judging by the links, the tool has several built-in functions (tSQLt Framework) that have a bunch of capabilities: with their help, you can test different database objects for different conditions. Let’s consider some examples:
1) You can check if the data in the two tables match.
2) You can check if a certain object is in the database (if it is there, then the test runs successfully, if not – it fails), etc.

Alas, it’s not possible to catch multiple outputs in T-SQL (a couple of sets). As a rule, this is carried out in .NET, which is a safe place for creating complicated tests. Lately, there has been a tendency to mostly conduct tests at the .NET level for MS SQL Server.

Anyway, let’s get back to T-SQL.

For simplicity’s sake, we comment out the output of detailed information in the stored procedure, that is, we comment out a query at the end of SearchEmployee:

SELECT
		tr2.[EmployeeID],
		tr2.[LastName],
	    tr2.[FirstName],
	    tr0.[CompanyID],
		(SELECT TOP(1) com.[CompanyName] FROM [dbo].[Company] AS com WHERE com.[CompanyID]=tr0.[CompanyID]) AS [CompanyName],
		tr0.[PositionID],
		(SELECT TOP(1) p.[PositionName] FROM [dbo].[Position] AS p WHERE p.[PositionID]=tr0.[PositionID]) AS [PositionName],
		tr0.[ProjectID],
		tr0.[StartDate],
		tr0.[FinishDate],
		tr0.[Description],
		tr0.[ProjectName],
		tr0.[ProjectDescription],
		tr0.[SkillID],
		tr0.[SkillName],
		tr0.[Achievements],
		tr0.[ReasonsForLeavingTheProject],
		tr0.[ReasonsForLeavingTheCompany]
	FROM #tbl_res2 AS tr2
	INNER JOIN #tbl_res0 AS tr0
		ON tr0.[EmployeeID] = tr2.[EmployeeID]
	INNER JOIN #tbl_skill AS ts
		ON ts.[SkillID] = tr0.[SkillID];

In our case, the test is rather simple, and it is going to check the following rule: there must be data, otherwise, it will output a message that the skills may have been incorrect, and the stored procedure does not function the way it should.

A failure to implement the rule indicates that either the data is absent and has to be searched based on different criteria, or the criteria have been defined incorrectly and have to be fixed. It can also mean that the stored procedure itself fails to operate properly.

So, we return to the code we generated earlier and change it as follows:

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE CL1.[test UT_SearchEmployee_Exception]
AS
BEGIN
  CREATE TABLE #tbl ([EmployeeID] INT, [LastName] NVARCHAR(255), [FirstName] NVARCHAR(255),
  [SkillID] INT, [SkillName] NVARCHAR(255), [StartDate] DATETIME, [FinishDate] DATETIME,
  [Years] INT, [Months] INT, [Days] INT);
  
  INSERT INTO #tbl
  EXEC [dbo].[SearchEmployee] @SkillList = N'programming'
  						   ,@CountNotSkill = 1;
  
  IF(NOT EXISTS(SELECT TOP(1) 1 FROM #tbl))
	EXEC tSQLt.Fail 'Nothing found. Check input parameters and stored procedure code';
  
  DROP TABLE #tbl;
END;

Now, let’s do one more right-click on the JobEmpl database and in the drop-down menu, select the Unit Test\” View Test List” command:

Img.9. Calling the View Test List command

After that, we choose the test we need and run it:

Img.10. Running the given test

As we can see below, the test was not successful:

Img.11.Running the unit test resulted in an error

It is important to stress that you can select multiple unit tests and launch them all, or just several of them. If necessary, you can create and delete a test. Here, we will open the selected test by clicking the “Open Test” command:

Img.12.Selecting a command to open the unit test

We want to change the code to the following:

--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE CL1.[test UT_SearchEmployee_Exception]
AS
BEGIN
  CREATE TABLE #tbl ([EmployeeID] INT, [LastName] NVARCHAR(255), [FirstName] NVARCHAR(255),
  [SkillID] INT, [SkillName] NVARCHAR(255), [StartDate] DATETIME, [FinishDate] DATETIME,
  [Years] INT, [Months] INT, [Days] INT);
  
  INSERT INTO #tbl
  EXEC [dbo].[SearchEmployee] @SkillList = N'C#;T-SQL;ASP.NET'
  						   ,@CountNotSkill = 1;
  
  IF(NOT EXISTS(SELECT TOP(1) 1 FROM #tbl))
	EXEC tSQLt.Fail 'Nothing found. Check input parameters and stored procedure code';
  
  DROP TABLE #tbl;
END;

Next, let’s run the test again:

Img.13. The successful completion of a unit test

As a result, the test ran successfully. The error simply occurred because the “programming” skill was not in the database, and we had to further detail the skill.

As well as that, we can rename the test class and the test itself and update the information on the tests:

Img.14. The possibility to rename and update the test class information

You can also run tests and open changes at the bottom of the window in Test Result:

Img.15. The Test Result functionality

Eventually, you can cover most or even the whole functionality of the created solution with the help of unit tests. The bottom line is that unit tests are part of the DevOps fundamental principles as they play one of the key roles in this automation process. If you strive to speed up and secure database release, you can apply one of several solutions for DevOps automation, such as DevOps Automation for SQL Server.