SQL Server Unit Testing with tSQLt Framework

In this article, we will talk about the tSQLt framework — a free database unit testing framework for Microsoft SQL Server. You can use tSQLt to create unit tests for SQL Server 2005 (service pack 2 required) and all further versions. The main convenience is that you do not need to switch between various tools to create code and unit tests.

tSQLt Key Features

Tests run automatically within transactions
This keeps tests independent and reduces any cleanup work you need.

Tests are grouped together within a schema
This allows you to organize your tests and use common setup methods.

Output can be generated in plain text or XML
This makes it easier to integrate with a continuous integration tool.

The ability to fake tables and views, and to create stored procedure spies
This allows you to isolate the code which you are testing.

Unit Test Your Database Code in SSMS Instantly

Before we proceed further, I would like to introduce you an extremely useful SSMS plugin from Devart — dbForge Unit Test for SQL Server. The tool provides a user-friendly and intuitive GUI designed to create, organize and execute test cases with a few points and clicks. It completely automates and simplifies the manual and time-consuming process of unit testing. The tool also cuts down the time spent on unit testing and brings significant cost savings.

Installing the framework

tSQLt is the open source framework and is distributed under the Apache 2.0 license. You can download the latest version of the tSQLt framework from the official site. 

Before installing the framework, you need to setup the SQL Server instance to work with CLR.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Also, you need to set to ON the TRUSTWORTHY database property. This property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, however, it can be set to ON by using the ALTER DATABASE statement.

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'ALTER DATABASE ' +
           QUOTENAME(DB_NAME()) +
           ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

The archive contains a script. You need to execute the script on a target database. As a result of execution, the script creates the tSQLt scheme, a CLR assembly and a number of procedures and functions. Some procedures will contain the Private_ prefix. The framework utilizes such procedures for the internal needs.

Creating Test Cases

A test is a stored procedure. For the convenience, tests are combined into “classes” and constitute SQL Server schemes. Schemas have some special properties applied so that tSQLt recognizes it as a test class. You can use the NewTestClass procedure to create a new class.

EXEC tSQLt.NewTestClass 'TestClassName'

Each class can have its own SetUp procedure, which will be called before each test.

CREATE PROCEDURE [TestClassName].[SetUp]
AS
BEGIN
    PRINT 'Do something';
END;
GO

While creating a new test class, if there is a schema with the same name, all objects in that schema (and the schema itself) are dropped. To create a test case, you need to create a stored procedure on your test class. You can use quotes inside the CREATE PROCEDURE statement to include whitespaces in the test name:

CREATE PROCEDURE [TestClassName].[test addNumbers computes
 3 plus 3 equals 6]
AS
BEGIN
    DECLARE @actualComputedResult INT;
    SET @actualComputedResult = dbo.addNumbers(3, 3);
    EXEC tSQLt.AssertEquals 6, @actualComputedResult;
END;
GO

Running Test Cases

You can use the RunAll of Run procedures to run tests all at once (on all classes) or one at a time.

-- Run all tests
EXEC tSQLt.RunAll;
 
-- Run all tests of a specific class
EXEC tSQLt.Run 'TestClassName';
 
-- Run a specific test of a specisic class
EXEC tSQLt.Run 'TestClassName.TestName'

You can call the Run procedure in three different ways:

1. With a test class name — all tests within the specified class name will be executed.
2. With the qualified schema name and the test case name
3. With no parameters

The procedure “remembers” what parameter was used the last time it was called. If the procedure is called with no parameter, it executes the last executed test case or test class.

Isolating Dependencies

If you ever used any framework for unit tests, you might be surprised to discover that tSQLt is no different. One of great features of tSQLt is “test isolation” that is based on the transaction mechanism. In addition, tSQLt provides useful procedures that help to determine what went wrong in the test.

Typical test consists of three parts:

1. Preparing environment (test data)
2. Executing code under test
3. Verifying results

There are some objects that allow you to isolate code which you are testing. If you are testing a complex system, a good practice is to isolate specific parts of the test. The following objects allow you to focus on a specific unit.

Preparing Environment

At this point, you need to prepare the database objects that will be used by the code under test. In other words, you need to substitute those objects with placeholders, stub and mock objects. What you can substitute:

FakeTable creates a copy of a target table with no data:

tSQLt.FakeTable [@TableName = ] 'table name'
                , [[@SchemaName = ] 'schema name']
                , [[@Identity = ] 'preserve identity']
                , [[@ComputedColumns = ] 'preserve computed columns']
                , [[@Defaults = ] 'preserve default constraints']

FakeTable allows tests to be written in isolation of the constraints on a table. FakeTable creates an empty version of the table without the constraints in place of the specified table. Therefore any statements which access the table during the execution of the test case are actually working against the fake table with no constraints. When the test case completes, the original table is put back in place because of the rollback which tSQLt performs at the end of each test case.

By default, calculated fields, default values and identity columns are not saved, however, this can be changed using the optional parameters: @identity, @ComputedColumns и @Defaults. The function can not substitute temporary tables, objects in other databases, as well as it does not preserve foreign keys. The function will create the Stub placeholder that you can fill in with the test data, without the need to change a real object. This will give you the opportunity to run the tests independently (multiple users can simultaneously run tests on a single instance of SQL Server).

FakeFunction will substitute a real function with the Stub placeholder:

tSQLt.FakeFunction [@FunctionName = ] 'function name'
                 , [@FakeFunctionName = ] 'fake function name'

Code that calls a function can be difficult to test if that function performs significant logic. We want to isolate the code we are testing from the logic buried in the functions that it calls. To create independent tests, we can replace a called function with a fake function. The fake function will perform much simpler logic that supports the purpose of our test. Often, the fake function will simply return a hard-coded value. Alternatively, the fake function may ‘validate’ the parameters it receives by returning one value if the parameters match expectations, and another value if the parameters do not match expectations. That way the code that calls the function will have a different result and thus the parameter passed to the function can be tested.

SpyProcedure creates the Mock object.

tSQLt.SpyProcedure [@ProcedureName = ] 'procedure name'
                [, [@CommandToExecute = ] 'command' ]

To create independent tests, we can replace the functionality of a stored procedure with a spy. The spy will record the parameters that were passed to it. SpyProcedure allows tests to be written for a procedure in isolation of the other procedures that it calls. SpyProcedure creates a table with the name of @ProcedureName + ‘_SpyProcedureLog’. This table contains an identity column ‘_id_’ and a column for each procedure parameter (except for cursor output parameters). SpyProcedure also replaces the procedure named by @ProcedureName with the command provided in the @CommandToExecute parameter and a command to insert the parameter values into the SpyProcedureLog table. Therefore, whenever the @ProcedureName is executed during the test instead of actually running the procedure, a new log entry is made in the @ProcedureName_SpyProcedureLog table and @CommandToExecute is called.

Executing code under test

This is the easy part. You simply run the code that you want to test. Nore that if you expect the test code creates an exception, you must notify tSQLt by calling the ExpectException procedure.

tSQLt.ExpectException
      [  [@ExpectedMessage= ] 'expected error message']
      [, [@ExpectedSeverity= ] 'expected error severity']
      [, [@ExpectedState= ] 'expected error state']
      [, [@Message= ] 'supplemental fail message']
      [, [@ExpectedMessagePattern= ] 'expected error message pattern']
      [, [@ExpectedErrorNumber= ] 'expected error number']

Verifying Results

There is a number of assertion stored procedures. You can use them to compare values. Also, there is the Fail stored procedure which marks a test case as failed.

Example

The CalcAvgTemperature procedure calculates an average temperature for a specified time span, based on the data in the temperature table. The PrintAvgTemperatureLastFourDaysprocedure uses CalcAvgTemperature to calculate the temperature for the last four days.

CREATE TABLE temperature
(
    DateMeasure DATE,
    Value numeric (18,2)
)
 
GO
 
-- Calculate an average temp for a time span
CREATE PROC CalcAvgTemperature
    @StartDate DATE,
    @EndDate DATE,
 
    @AvgTemperature numeric (18,2) OUT
AS
BEGIN
 
    SELECT @AvgTemperature = AVG(Value)
    FROM temperature
    WHERE DateMeasure BETWEEN @StartDate AND @EndDate
 
END
 
GO
 
-- Print average temp for 4 days
CREATE PROC PrintAvgTemperatureLastFourDays
    @Date DATE,
 
    @TemperatureString VARCHAR(255) OUT
AS
BEGIN
 
    DECLARE
        @StartDate DATE = DATEADD(D, -3, @Date),
        @EndDate DATE = @Date,
        @Result numeric (18,2)
 
    EXEC CalcAvgTemperature @StartDate, @EndDate, @Result OUT
 
    SET @TemperatureString  =
        'Средняя температура с ' +
        CONVERT(VARCHAR,@StartDate,104) +
        ' по ' +
        CONVERT(VARCHAR,@EndDate,104) +
        ' равна ' +
        CONVERT(VARCHAR,@Result)
END

Now, we can create a new test class.

EXEC tSQLt.NewTestClass 'TemperatureTests'

Now, we can add one test for each procedure.

-- Test for PrintAvgTemperatureLastFourDays
CREATE PROC TemperatureTests.Test_PrintAvgTemperatureLastFourDays
AS
BEGIN
 
    -- Preparing environment
 
    -- Substitute CalcAvgTemperature with a placeholder,
    -- that always returns 100.00
    EXEC tSQLt.SpyProcedure
            'CalcAvgTemperature',
            'SET @AvgTemperature = 100.00'
 
    -- Run procedure
 
    DECLARE @TemperatureString VARCHAR(255)
    EXEC PrintAvgTemperatureLastFourDays
            '2014-08-04',
            @TemperatureString OUT
 
    -- Results verification
 
    -- Get arguments for CalcAvgTemperature
    SELECT StartDate, EndDate
    INTO actual
    FROM CalcAvgTemperature_SpyProcedureLog
 
    -- a table with expected results
    CREATE TABLE expected
    (
        StartDate DATE,
        EndDate DATE
    )
 
    INSERT expected (StartDate, EndDate)
    VALUES ('2014-08-01', '2014-08-04')
 
    -- Compare actual and expected argument
    EXEC tSQLt.AssertEqualsTable
        'expected',
        'actual',
        'CalcAvgTemperature called with wrong arguments'
 
    -- Compare actual and expected result string
    EXEC tSQLt.AssertEqualsString
        'Average temperature from 01.08.2014 to 04.08.2014 is equal 100.00',
        @TemperatureString,
        'Wrong string format'
 
END
 
GO
 
-- Test for CalcAvgTemperature
ALTER PROC TemperatureTests.Test_CalcAvgTemperature
AS
BEGIN
 
    -- Preparing environment
 
    -- Faking temperature
    EXEC tSQLt.FakeTable 'temperature'
 
    -- Fill in the fake table with test data
    INSERT temperature (DateMeasure, Value)
    VALUES
    ('2014-08-04', 26.13),
    ('2014-08-03', 25.12),
    ('2014-08-02', 26.43),
    ('2014-08-01', 20.95)
 
    -- Run the procedure
 
    DECLARE @AvgTemperature numeric(18,2)
    EXEC CalcAvgTemperature
            '2014-08-01',
            '2014-08-04',
            @AvgTemperature OUT
 
    -- Verifying results
 
    -- Compare actual and expected results
    EXEC tSQLt.AssertEquals
        24.66,
        @AvgTemperature,
        'Average temperature calculated with an error'
 
END

To run both tests, we can use the Run procedure and pass it the name of our test class (TemperatureTests).

EXEC tSqlt.Run 'TemperatureTests'

As it is expected, tests are successful.

+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                            |Result |
+--+----------------------------------------------------------+-------+
|1 |[TemperatureTests].[Test_CalcAvgTemperature]              |Success|
|2 |[TemperatureTests].[Test_PrintAvgTemperatureLastFourDays]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Special aspects

Do not forget that when you run a test, tSQLt wraps it in the transaction. Therefore, be careful if your stored procedure uses it’s own transactions. For example, the following test procedure fails:

CREATE PROC [IncorrectTran]
AS
BEGIN
 
    BEGIN TRAN TestTran
 
    BEGIN TRY
 
        SELECT 1 / 0
 
        COMMIT TRAN TestTran
 
    END TRY
    BEGIN CATCH
 
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN TestTran
 
    END CATCH
 
END

Although outside the test, the procedure will work without error. The reason is that ROLLBACK will undo both – your and tSQLt transactions. At the end of the procedure, the number of active transactions change. This problem is described here and the solution for this problem is here.

Conclusion

This article gave you a brief introduction to creating test cases for SQL Server. We have discussed the tSQLt unit test framework for SQL Server.

Useful tool:

dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.