Unit Tests for SPs Using tsqlt Framework

In this article, see how to easily test Stored Procedures by adding unit tests using tsqlt framework.

Gowthamraj Palani

Sep. 13, 22 · Tutorial

Likes (1)

Comment

Save

5.1K Views

Writing unit tests to test the code is an inevitable practice in programming. Testing stored procedures are as important as the other code. Often SPs are tested manually, and we avoid adding an automated test. In an agile environment, code without tests are prone to defects at some point. In this article, let's see about tsqlt framework - an open source Database Unit Testing framework for SQL Server.

Pre-Requisites

Code Editor (ex., IntelIj)
SQL Database

Attach your Editor to the running database. The database can be running either in the local, cloud or in any VMs.

Create Test Objects

Create a new Test class as below. Running this in the attached session of DB will create a test object in DB. We will be adding all the test scripts (SPs) inside this object.

     SQL 
   
   EXEC  tsqlt.NewTestClass 'EMPLOYEE'
GO

Note the tests inside the newly created object. We will be reading about the tests in detail below. We are all set now to write our tests.

Table and SP Details

Below are the table details and SP for the example that we are going to see in this article.

Table Name: Employee with columns emp_no, emp_name, and salary.

Stored Procedure 1: To select rows from the table.

     SQL 
   
 
 
   CREATE PROCEDURE [dbo].[get_all_employees_v1]
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT emp_no
        , emp_name
        , salary
    FROM dbo.employee c
END
GO
 
  

Stored Procedure 2: To insert a new record.

     SQL 
   
 
 
   
CREATE PROCEDURE [dbo].[update_employees_v1]
    @emp_no INT
   ,@emp_name VARCHAR(20)
   ,@salary INT

AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    INSERT INTO dbo.employee (emp_no, emp_name, salary)
    VALUES (@emp_no, @emp_name, @salary)
END
GO
 
  

Let's see how to write tests for the above SPs.

Test Patterns

We write tests in different structure for SPs that returns result and updates records. There is no major difference in both but a simple difference in populating expected and actual tables.

For Select:

Create fake tables.
Insert test data into tables.
Create expected and actual tables.
Run SP and put the result in the "actual" table.
Compare "expected" and "actual" tables.

     SQL 
   
 
 
   CREATE OR ALTER PROCEDURE [EMPLOYEE].[test employee]
AS
BEGIN
    -- ARRANGE (fake synonym table employee is created)
    EXEC tSQLt.FakeSynonymTable 'employee', 'dbo';

-- Test data insertion
    INSERT INTO dbo.employee (emp_no, emp_name, salary) VALUES (2, 'xxx', 1000);

-- Actual table is defined
    SELECT emp_no, emp_name, salary INTO #actual FROM dbo.employee where 1 = 0

-- Create an empty #Expected temp table that has the same structure as the #Actual table
    SELECT TOP (0) *
    INTO #expected
    FROM #actual;

-- Run the SP and store results in actual table
    INSERT INTO #actual
        EXEC dbo.get_all_employees_v1

-- Populate expected table with desired results
    INSERT INTO #expected (emp_no, emp_name, salary) VALUES (2, 'xxx', 1000);

-- Assert expected and actual
    EXEC tSQLt.AssertEqualsTable '#expected', '#actual', 'missing expected row'

END
GO 
  

Run the test using the code below and see the tests pass:

EXEC tSQLT.RUN 'EMPLOYEE.[test employee]'

Insert or Update or Delete:

Create fake tables.
Insert test data into tables.
Create expected and actual tables.
Run the SP.
Copy records that are modified from the original table to actual.
Compare "expected" and "actual" tables.

     SQL 
   
 
 
   CREATE OR ALTER PROCEDURE [EMPLOYEE].[test update_employee]
AS
BEGIN
    -- ARRANGE (fake synonym table employee is created)
    EXEC tSQLt.FakeSynonymTable 'employee', 'dbo';

-- Create expected tables
    CREATE TABLE #expected (emp_no int, emp_name VARCHAR(20), salary int)

-- Run the SP
    EXEC dbo.update_employees_v1 @emp_no=1, @emp_name='test', @salary=300

-- Above step had already create a new record in the table. Read it and store it in actual table
    SELECT emp_no, emp_name, salary INTO #actual FROM dbo.employee WHERE emp_no=1

-- Populate expected table with desired results
    INSERT INTO #expected (emp_no, emp_name, salary) VALUES (1, 'test', 300);

-- Assert expected and actual
    EXEC tSQLt.AssertEqualsTable '#expected', '#actual', 'missing expected row'

END
GO 
  

Run the test using:

EXEC tSQLT.RUN 'EMPLOYEE.[test employee]'

Conclusion

Writing tests for any piece of code is important to prevent bugs. SPs are no different. Writing automated tests for SPs is a good option to test them very early and efficiently.

Framework Testing

Opinions expressed by DZone contributors are their own.

Related

Trending