Automating tests for T-SQL code

Introduction

Recently I had to write a lot of T-SQL code and I decided to approach the task of unit testing it as rigorously as possible. There are several T-SQL test tools available that seem to follow the model established by unit test tools from languages like Java and C#. Some of these are: spunit, Sqltdd, utTSQL, TSQLUnit, etc. I soon realized that in a real life environment there was a need for features that the existing tools were either missing or did not implement in a reliable way. Hence, yet another tool was born: T.S.T. (T-SQL Test Tool).

Eventually I released it as an open source project hosted at http://tst.codeplex.com. At the same place you can find complete documentation and samples. A 5 minutes video demo is also available at http://www.youtube.com/watch?v=uGkGSkoh-CE

Here are some of the areas where T.S.T. improves upon:

Reliable comparison of values of different SQL types
Since T-SQL does not have method overloading, most of the existing tools use the sql_variant data type for the parameters used in the comparison procedures (think AssertEquals). Comparing two sql values of different types that were converted to sql_variant can yield outcomes that can be surprising to many people. Some of the T-SQL test tools are not prepared to handle all the possible scenarios and perform an unreliable comparison.
T.S.T. has a reliable implementation of Assert.Equals / Assert.NotEquals procedures. They automatically detect when they are used incorrectly due to incompatibility of the data types. Additional procedures like Assert.NumericEquals, Assert.NumericNotEquals, Assert.FloatEquals, Assert.FloatNotEquals are provided.
Details:
To illustrate this problem consider the following procedure that tests the equality of two values:
```
   CREATE PROCEDURE TestEquals 
     @Expected sql_variant,
     @Actual sql_variant
   AS
   BEGIN
     -- We'll ignore the NULL case for this simple example
     IF @Expected = @Actual
       PRINT 'OK'
     ELSE
       PRINT 'Error'
     END
   GO
   
```
And now invoke this validation in a code like:
```
   DECLARE @MyVar float
   SET @MyVar = 1.5
   EXEC dbo.TestEquals 1.5, @MyVar
   
```
The output will show 'Error' even though you may be tempted to expect 'OK'. This is because when TestEquals is executed @Expected and @Actual will contain values not only of different data types (that in itself is not enough to fail the comparison) but values of different data type families. In this case @Expected contains a value of type numeric which belongs to the 'exact numeric' data type family. @Actual contains a value of type float which belongs to the 'approximate numeric' data type family.
Table comparison
When it comes to validating tables returned by stored procedures, functions or views many existing test tools don't provide a lot of help. T.S.T. has a convenient support for comparing results in table form by providing an API: Assert.TableEquals.
Other features needed to facilitate integration with build processes and more advanced uses
Some of the features that T.S.T. provides that are useful in this context:
> Can produce results in an XML format.
> Can run concurrent test sessions against the same or multiple databases. This is useful for cases where one or more build systems installed on the same server run concurrent build processes.
> Can be triggered from the command prompt, from SQL Management Console or programmatically.
> Test stored procedures don't have to be registered; they are picked-up automatically by the tool.
> Has a reliable automatic rollback mechanism.

Using the T.S.T. T-SQL test tool

Installing the tool

You can download the tool from http://tst.codeplex.com. There go to the "Downloads" tab, and click on the link under "Downloads & files". Extract the content of the ZIP file on a local folder. The content contains no binaries - only scripts and documentation. Open a command prompt, go to that location and run "TST.BAT" This will install a SQL database called TST which is all you need to start testing your code.

Playing with the quick start sample

The tool comes with a quick start sample code. This was written to illustrate most of the features that T.S.T. offers. To install the quick start sample database open a command prompt, go to the location where you have TST.BAT and run:

TST.BAT /QuickStart

This will install a SQL database called TSTQuickStart that contains sample code and tests. Once this is done, you can treat TSTQuickStart as any regular database that contains T.S.T. test procedures. For example, to execute all the tests contained there, go in the command prompt and run:

TST.BAT /RunAll /TSTQuickStart

The output that results is shown below:

Writing test procedures

Let's say we have a function called QFn_AddTwoIntegers. As its name suggests it adds two integers. Here is an example of a test for this function:

CREATE PROCEDURE dbo.SQLTest_AddTwoIntegers
AS
BEGIN

   DECLARE @Sum int
   SELECT @Sum = dbo.QFn_AddTwoIntegers(1,1)
   EXEC TST.Assert.Equals '1 + 1 = 2', 2, @Sum

END
GO

In the next sections we'll go in more detail about what we may have inside a test procedure. For now it is enough to point out that writing a test is as simple as creating a stored procedure with no parameters and a name prefixed with 'SQLTest_'. The test runners provided by T.S.T. will recognize that stored procedure as a test based on this prefix.
There are similar naming conventions to group tests into suites and to provide set-up and teardown procedures. Let's say that we want to group all the tests regarding the authentication procedures in a test suite called 'Authentication'. The test procedures will be declared as follows:

CREATE PROCEDURE dbo.SQLTest_SETUP_Authentication ...
CREATE PROCEDURE dbo.SQLTest_TEARDOWN_Authentication ...
CREATE PROCEDURE dbo.SQLTest_Authentication#LoginInvalidPassword ...
CREATE PROCEDURE dbo.SQLTest_Authentication#LoginInvalidUserName ...
CREATE PROCEDURE dbo.SQLTest_Authentication#LoginValidUser ...

When T.S.T. is directed to run the suite 'Authentication', it will isolate the procedures above based on their names and will run them in the following order:

SQLTest_SETUP_ Authentication
SQLTest_ Authentication#LoginInvalidPassword
SQLTest_TEARDOWN_Authentication
SQLTest_SETUP_ Authentication
SQLTest_ Authentication#LoginInvalidUserName
SQLTest_TEARDOWN_Authentication
SQLTest_SETUP_ Authentication
SQLTest_Authentication#LoginValidUser
SQLTest_TEARDOWN_Authentication

Important: Teardowns should be avoided unless there is a need to do more than simply rolling back changes. By default the TST framework automatically rolls back all the changes made in the Setup/Test/Teardown at the end of each test. This makes the teardown unnecessary in most scenarios. The rollback mechanism is described later in this article.

How to run the tests

You can run all the tests in a database in the command prompt by running the command:

TST.BAT /RunAll DatabaseName

You can also trigger one specific suite by running:

TST.BAT /RunSuite DatabaseName SuiteName

To use as an example the names from the previous section:

TST.BAT /RunSuite DatabaseName Authentication

Or you can trigger one specific test by running:

TST.BAT /RunTest DatabaseName TestName

An example of this would be:

TST.BAT /RunTest DatabaseName SQLTest_Authentication#LoginValidUser

You can also run all the tests in the SQL Management Console by executing a runner stored procedure:

EXEC TST.Runner.RunAll 'DatabaseName'

Or you can run one suite by executing:

EXEC TST.Runner.RunSuite 'DatabaseName', 'SuiteName'

Or you can run one test by executing:

EXEC TST.Runner.RunTest 'DatabaseName', 'TestName'

Using the tool to validate values

Let's take a very simple case where we are going to test a function called dbo.QFn_TinyintToBinary. This function converts an integer to a string containing its binary representation. For example it converts 10 into '1010'. We'll pass in a value, obtain a result and then validate it against its expected result. We will repeat this with several values. To implement this, we create the following test stored procedure:

CREATE PROCEDURE SQLTest_QFn_TinyintToBinary
AS
BEGIN
   DECLARE @BinaryString varchar(8)
   SET @BinaryString = dbo.QFn_TinyintToBinary(NULL)
   EXEC TST.Assert.IsNull 'Case: NULL', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(0)
   EXEC TST.Assert.Equals 'Case: 0', '0', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(1)
   EXEC TST.Assert.Equals 'Case: 1', '1', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(2)
   EXEC TST.Assert.Equals 'Case: 2', '10', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(129)
   EXEC TST.Assert.Equals 'Case: 129', '10000001', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(254)
   EXEC TST.Assert.Equals 'Case: 254', '11111110', @BinaryString
   SET @BinaryString = dbo.QFn_TinyintToBinary(255)
   EXEC TST.Assert.Equals 'Case: 255', '11111111', @BinaryString
END
GO

Using the tool to validate views, stored procedures or functions that return a table

A more interesting case is when we have to validate a table that is returned by a stored procedure or maybe a function or a view. T.S.T. offers a specialized API for this task: Assert.TableEquals. Let's say that we have a stored procedure called GetDirectReports that returns some data about all the direct reports of a manager. Our test procedure may look something like this:

CREATE PROCEDURE SQLTest_GetDirectReports
AS
BEGIN
   -- Create the test tables #ActualResult and #ExpectedResult. 
   -- They must have the same schema as the table returned 
   -- by the procedure GetDirectReports
   CREATE TABLE #ExpectedResult (
      EmployeeId int PRIMARY KEY NOT NULL,
      EmployeeFirstName varchar(256),
      EmployeeLastName varchar(256)
   )
   CREATE TABLE #ActualResult (
      EmployeeId int PRIMARY KEY NOT NULL,
      EmployeeFirstName varchar(256),
      EmployeeLastName varchar(256)
   )
   -- This is where we set-up our scenario. For example we 
   -- could insert records in a employee table that will 
   -- generate a relevant scenario for calling GetDirectReports.
   -- ... ... ...
   -- ... ... ...

   -- Store the expected result in #ExpectedResult
   INSERT INTO #ExpectedResult VALUES(10, 'Mary' , 'Jones' )
   INSERT INTO #ExpectedResult VALUES(11, 'Michael', 'Garcia' )
   INSERT INTO #ExpectedResult VALUES(12, 'Linda' , 'Moore' )
   -- Call GetDirectReports and store the result in #ActualResult
   INSERT INTO #ActualResult EXEC GetDirectReports
   -- Now compare the actual vs. expected data.
   -- Assert.TableEquals compares the schema and content
   -- of tables #ExpectedResult and #ActualResult.
   EXEC TST.Assert.TableEquals 'Some contextual message here'
END
GO

When the table that we validate contains columns that are nondeterministic (like timestamps) we won't be able to predict their 'correct values'. We can exclude those columns from the validation by using an optional parameter of Assert.TableEquals. For example if our table contains two columns called [Create Date] and [Modified Date] we can skip them from the validation by calling:

EXEC TST.Assert.TableEquals 
   @ContextMessage = '...', 
   @IgnoredColumns = 'Create Date;Modified Date'

If we have to validate a table returned by a function instead of a stored procedure then the line:

INSERT INTO #ActualResult EXEC GetDirectReports

will have to be changed to something like:

INSERT INTO #ActualResult SELECT * FROM dbo.QFn_GetDirectReports()

And if this is a test that validates a view then we will write something like:

INSERT INTO #ActualResult SELECT * FROM dbo.VW_DirectReports

Of course, in these two last cases we can explicitly specify the columns that we want to transfer in the table #ActualResult.

Using the tool to validate errors

T.S.T. can be used to validate the scenarios where we expect certain errors to occur:

CREATE PROCEDURE SQLTest_ExpectedError
AS
BEGIN
   EXEC TST.Assert.RegisterExpectedError 
      @ContextMessage = 'Some contextual message here',
      @ExpectedErrorMessage = 'Test error'
   -- SomeSprocThatRaisesAnError is the unit under test 
   -- and we expect that it will raise an error by executing: 
   -- RAISERROR('Test error', 16, 1)
   EXEC dbo.SomeSprocThatRaisesAnError
END
GO

Note: The API RegisterExpectedError has a few more parameters that allow for a more complex usage.

Automatic Rollback

One of the important issues you will have to deal with when testing T-SQL code is how to clean-up after one test so that the changes it made won't interfere with subsequent tests. The default behavior of T.S.T is to wrap a test in a transaction and rollback all the changes at the end. That includes changes done during the set-up, the test itself and the teardown procedure. And since the roll back is automatic most of the time you should not have to write a teardown procedure at all.

If the code that you are testing does not use transactions or if it does a BEGIN TRANSACTION / COMMIT TRANSACTION, then you are fine and the automatic rollback will work as expected.

However, if the code you are testing does a BEGIN TRANSACTION / ROLLBACK TRANSACTION, that rollback will interfere with the transaction opened by the T.S.T. framework. In SQL Server, a ROLLBACK TRANSACTION executed in a nested transaction causes the rollback to propagate to the outermost level. This will in effect terminate the transaction opened by T.S.T. and have all the subsequent changes executed outside of a transaction. That will render the TST Rollback useless.

T.S.T. will detect the cases where the automatic rollback cannot function as expected. In those cases it will fail the corresponding test with an error indicating what happened. If that is the result of a bug in your test or in your unit under test then you should be able to fix it. If that is the result of a legitimate scenario you have the option of disabling the automatic rollback and do the clean-up on your own in a teardown procedure.

The T.S.T. API

Just to get another idea about the scope of the tool, here is the list of procedures that form the T.S.T. API:

Assert.LogInfo
Assert.Pass
Assert.Fail
Assert.Equals
Assert.NotEquals
Assert.NumericEquals
Assert.NumericNotEquals
Assert.FloatEquals
Assert.FloatNotEquals
Assert.IsLike
Assert.IsNotLike
Assert.IsNull
Assert.IsNotNull
Assert.TableEquals
Assert.IsTableEmpty
Assert.RegisterExpectedError
Runner.RunAll
Runner.RunSuite
Runner.RunTest
Utils.SetConfiguration

Conclusion

This tool was designed with the idea of making its adoption as inexpensive as possible. It can be triggered from the command prompt, from SQL Management Console or programmatically. It can produce XML results and it is able to run concurrent test session. All these should make the integration with existing build processes simple even for large scale projects. If you have a project that does not have an automated build process you can still run all your tests with only one command. The test runners will detect the test procedures based on naming conventions. This means there is no registration process of the tests so you don't have to incur additional costs maintaining that. Hopefully all these things will make it an attractive tool to use for anyone who wants to automate its T-SQL tests.

Automating tests for T-SQL code

Introduction

Using the T.S.T. T-SQL test tool

Installing the tool

Playing with the quick start sample

Writing test procedures

How to run the tests

Using the tool to validate values

Using the tool to validate views, stored procedures or functions that return a table

Using the tool to validate errors

Automatic Rollback

The T.S.T. API

Conclusion

Rate

Share

Categories

Share

Rate

Automating tests for T-SQL code

Introduction

Using the T.S.T. T-SQL test tool

Installing the tool

Playing with the quick start sample

Writing test procedures

How to run the tests

Using the tool to validate values

Using the tool to validate views, stored procedures or functions that return a table

Using the tool to validate errors

Automatic Rollback

The T.S.T. API

Conclusion

Rate

Share

Categories

Share

Rate

Related content

Free Database Unit-Testing Framework for SQL Server

Why don’t you unit test SQL Server code?

SQL Server Unit Testing in Visual Studio

Is It Worth Writing Unit Tests?

Unit Testing with SQL Server