One of the primary goals of the tSQLt framework is to enable the testing of your T-SQL code. There are many different stored procedures, functions, and assertions you can use to verify that your code works as designed. In this short piece, we will look at the basics of unit testing a user defined function (UDF) with the framework.

If you aren't familiar with tSQLt, you can read about installing it here, or at the tsqlt.org website.

A Basic UDF

Here is a simple UDF that I used to calculate the reading time for a set of text. This is based on the idea that the average person reads about 250 words per minute. The function takes a string of text and determines the number of words by counting spaces. It then divides that by 250 to get the number of minutes it takes to read the text.

ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) )
returns int
as
    begin
      declare
        @ret as int = 1
      , @i as int = 1;
      while @i <= len(@value)
        begin
          if substring(@value, @i, 1) = ' '
            begin
              set @ret = @ret + 1;
            end
          set @i = @i + 1;
        end
      return @ret / 250;
;    end

Let's leave aside the idea that this code is correct. Assuming that this is the specification for the function and a scalar UDF works for us, how do we test this?

The details of the test are below, but let's examine what we want to test up front. Our first test should be for the "happy path" or the expected way in which this code will run. That means that if we pass in 250 words (or spaces) in a string, we should get 1 back as a result. If we pass in 1000 words, we should get 4. We don't need to test every single permutation of spaces here, but we should test at least one to verify the calculation work.

While this seems simple, imagine that two years from now someone changes this function, and perhaps uses a tally table to calculate the spaces. If the basic calculation remains the same (# spaces / 250), then this test will catch any programming errors the developer might introduce.

Writing a Test

The way that we write a unit test is to use the AAA framework. We Assemble our environment, Act on our code, and Assert something is true. This has also been known as Arrange/Act/Assert in some testing circles. Regardless of how to put together your environment, the intention is the same. We will break down our test into each section and then show the entire procedure together.

Assemble

In this test case, we need to assemble very little. We need to create a string that we will pass to our function and have a variable for the result. We also need an expected value that we can use for our assertion, and we'll set that up as a variable as well. Here is the Assemble code.

  DECLARE @article VARCHAR(MAX)
, @expresult INT
, @actresult int;
  SELECT @article = REPLICATE('alpha ', 1000);
  SELECT @expresult = 4;

Note that we are creating a string with 1000 words in it, and we will expect to see the result of 4 from our function. We could use any number of words here, but this is what I choose. Note that we're not setting any value for our actual result. If our test code is wrong or we fail to Act appropriately, we want the test to fail.

Act

Our Act code is very simple. We call the function. In this case, the code is very simple:

  SELECT @actresult = dbo.calculateEstimateOfReadingTime(@article);

If the function doesn't return any value, our test should fail here. The Act method, in this case the call to the UDF, is responsible for setting some value to our actual result.

Assert

The Assert phase is where we assert something is true. In this case, we will use the tSQLt AssertEquals method to compare two variables. We pass in our @Expected and @Actual variables for comparison. If they are equal, the test passes. If not, the test fails and the @Message output is captured as a failure text. 

Note that these variables being passed in are SQLVariant types, and we rely on an implicit conversion to take place.

  EXEC tsqlt.AssertEquals
    @Expected = @expresult, -- sql_variant
    @Actual = @actresult, -- sql_variant
    @Message = N'Incorrect calculation of reading time' -- nvarchar(max)
;

The Complete Code

That's our complete test, but it needs to be wrapped up for tSQLt to function. A tSQLt test is a stored procedure, so let's create one with our code.

create procedure [tArticles].[test calculateEstimateOfReadingTime_CheckCalculation]
as
begin
  --Assemble
  DECLARE @article VARCHAR(MAX)
, @expresult INT
, @actresult int;
  SELECT @article = REPLICATE('alpha ', 1000);
  SELECT @expresult = 4;
  -- act
  SELECT @actresult = dbo.calculateEstimateOfReadingTime(@article);
  -- assert
  EXEC tsqlt.AssertEquals
    @Expected = @expresult, -- sql_variant
    @Actual = @actresult, -- sql_variant
    @Message = N'Incorrect calculation of reading time' -- nvarchar(max)
;
end 
;
go

Test Execution

To execute our test against the system, we use the tsqlt.run stored procedere. We can run this from the SQL Test plugin, if we have that installed on our system, or directly from a query window. 

To use T-SQL to execute a test, I can run this code:

EXEC tsqlt.Run
  @TestName = N'[tArticles].[test calculateEstimateOfReadingTime_CheckCalculation]';

Note that I need to provide the schema and test name, which is best done with brackets as we have a space in the name of the test. This will invoke the tSQLt framework, create a transaction, and run the test inside of that transaction. Whether the test succeeds or fails, my database will be returned to that state it is in at the start of this test.

When I execute this, I receive these results:

The results of the last test run are also stored in the tsqlt.TestResult table, as you can see below.

I can also easily execute my test with SQLTest, using the GUI. If I right click a test (or test class/database), I can execute a test.

The green check mark shows that I've successfully executed a test. If there were a problem and the test failed, I'd see a red X instead.

Summary

This is a very basic example of testing a function and check your results against what you expect. You can check the results you expect to be returned from common data, but you can also check for results from edge cases or strange data. For example, you may also wish to check how this function handles NULL or blank strings as well as expected results. 

This may seem very simplistic for writing function code, but the real power of testing comes from regression testing, and running this test over and over as the function might be altered over time to meet new business needs. These tests can help you catch logic errors that might creep into your code over time.

I hope you can see how you might begin to build unit tests for your application and begin using the tSQLt framework to improve the quality of your T-SQL code.

Learn tSQLt and Unit Testing at SQL Saturday #393 in Philadelphia

Come learn more about tSQLt from Sebastian Meine and Steve Jones in Philadelpia on June 5, 2015. They are presenting a pre-conference seminar on Friday before the SQL Saturday on June 6, 2015. This is the chance to learn about what the tSQLt framework can do and write actual tests against your own SQL Server. A laptop is recommended to allow you to interactively work with code throughout the day.

Register today