The tSQLt framework is a unit testing framework designed to work with database code, specifically T-SQL code inside of SQL Server. This framework was developed by Sebastian Meine and Dennis Lloyd and is completely free for you to use. There is more information at the tsqlt.org site, but this article looks to introduce you to the framework and get you started writing your first test.

Note: This requires the CLR framework and will work in SQL Server 2005 SP2 and later. It does not work in Azure.

Installing tSQLt

The installation for tSQLt is very simple. The latest download is a .zip file that you copy to your machine and open up in the Windows Explorer. When you click the download link from tsqlt.org, you'll be redirected to Sourceforge.net and will see the download begin.

Latest Download Link

Once you download the file, you can open it in Explorer and see a few files. The only important one for the framework is the tsqlt.class.sql file. The rest are release notes and include information contained in this article.

Note: You do need to enable the CLR and the Trustworthy setting. The SetCLREnabled.sql file does this in the following way if you have no already done so.

UPDATE: If you use a tSQLt release after January 31, 2016, you do not need to enable Trustworthy

Once you've enabled these properties, you can now install the framework. If you open the tsqlt.class.sql file, you will see something similar to this:

The framework is written in T-SQL and the CLR, so it is installed as a set of T-SQL objects inside SQL Server. Note that your session context needs to be inside the database where you are installing the framework. The testing framework can be installed into any database separately, but it does need to be installed to each database in which you will test code.

Writing a Test

There's a lot to writing a test, but let's make a simple one. First, let's create a test class in which we can group our tests together. I'll choose to do this in the TestSample class.

EXEC tsqlt.NewTestClass @ClassName = N'TestSample';

Now I have a test class, so let's write a test. These tests are simple stored procedures that exists in our test class, which is a schema. I'll write a test that verifies a stored procedure returns a particular calculation. In this case, I'll assume the spDouble stored procedure doubles the value of an input parameter. Our test will set up an environment, including specifying the result we expect. We then test the stored procedure and assert a truth at the end. In this case, we assert that the expected value is the same as the actual value returned.

CREATE PROCEDURE [TestSample].[test spDouble Calculation]
AS
    BEGIN
-- assemble
        DECLARE
            @param INT
        ,   @expected INT
        ,   @actual INT;
        SET @param = 5;
        SET @expected = 10;
-- Act
        EXEC @actual = spDouble @param;
-- assert
        EXEC tSQLt.AssertEquals @Expected = @expected, @Actual = @actual,
            @Message = N'The calculation is incorrect.'; -- nvarchar(max)
    END;

Now we need that stored procedure to exist, so I'll create that.

CREATE PROCEDURE spDouble 
  @input int
AS
    BEGIN
        RETURN @input *2;
    END
GO

EXEC tsqlt.Run @TestName = N'[TestSample].[test spDouble Calculation]';

We need to include the class and test name to run the test. As you can see, this succeeds. If I were the change the procedure to something like @input * 3, the test would fail.

Conclusion

This is a quick introduction to getting tSQLt up and running in your environment. There is a lot more to learn and do with the framework. Be sure to read the documentation and articles at the tsqlt.org site, or follow along with our Stairway to Testing, which will cover the process of writing tests for your T-SQL code.