SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Installing tSQLt

By Steve Jones, (first published: 2015/01/08)

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
Now we can execute the test. We'll use a tsqlt call to do this, which ensures our test is run in a transaction.
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.

 
Total article views: 13399 | Views in the last 30 days: 2
 
Related Articles
BLOG

Updating tsqlt

I was looking to write a new test with the tsqlt framework recently. I wanted to isolate a stored pr...

ARTICLE

Testing Simple Calculations and UDFs with tSQLt

This article will give you a basic look at how you can test your user defined functions with the tSQ...

ARTICLE

Checking Table Metadata with tSQLt

In this article on tSQLt, learn how you can use the AssertResultSetsHaveSameMetaData method from the...

BLOG

Where do you install tSQLt?

The question of where to install tSQLt is probably the most common question I get when I talk abou...

ARTICLE

tSQLt and the INSERT EXEC Problem

A proposed solution to the INSERT EXEC problem in tSQLt

Tags
testing    
tsqlt    
 
Contribute