It's True (And Not False): Assert Equality in TSQLUnit

When working with xUnit style frameworks like NUnit, it is generally expected to find support for assertions. Asserts are an indispensable tool in any testing process. Unit testing at its core is simply verifying whether something has occurred or not occurred i.e. checking if the state has changed marking it as either true or false. Built-in assert syntax provides programmers a means to perform this level of testing repeatedly and consistently. One of the more common assertions is to compare the values of two items, such as variables or objects, sometimes referred to as equality asserts. As an example, here is C# test code using NUnit to test a method that simply adds two arguments:

// some static class
public static int Add(int firstItem, int secondItem)
{
  return firstItem + secondItem;
}

[Test]
public void Verify_sum_of_5_and_3_equals_8()
{
  Assert.AreEqual(Add(5,3), 8);
}

Imagine my disappointment to discover the absence of equality assert functionality in TSQLUnit. The framework inexplicably does not have any native support for them. (Perhaps this might be because the project has not been active for a very long time.) Instead, you have to construct your own tailor-made assertions outside of the framework itself.

While using TSQLUnit without the aid of asserts, a definite pattern emerged as tsql plumbing code rapidly began to replicate in numerous tests. Here is an example of a unit test, plagued with the same identical code found in multiple places, whose purpose is to verify whether a 'ProductNumber' column has been successfully updated:

create procedure dbo.ut_CanUpdateProductNumber
/* UNIT TEST */
as
begin
  declare @expectedProductNumber    varchar(40),
          @actualProductNumber    varchar(40),
          @currentAccountID        varchar(16)

  set @expectedProductNumber = '1234567890'

  -- find a account to test
  select top 1 @currentAccountID=AccountNbr
  from dbo.Account

  /* RUN TEST */
  -- update product number
  update dbo.Account
  set ProductNumber = @expectedProductNumber
  where AccountNbr = @currentAccountID

  -- get updated product number
  select @actualProductNumber=l.ProductNumber
  from dbo.Account l
  where l.AccountNbr = @currentAccountID
 
  /* ASSERT TEST */
  if  @actualProductNumber != @expectedProductNumber
      begin     
          exec tsu_failure 'The product number is not the same.'
          print 'Expected: '    + @expectedProductNumber
          print 'Actual: '    + @actualProductNumber     
      end
end

The duplicated code is the conditional 'if' block at the end of the stored procedure where the assertion is executed using 'tsu_failure', the obligatory proc call to the TSQLUnit framework that transforms your tsql code into a real live unit test. All test sprocs are built around this critical function. Unfortunately, 'tsu_failure' does not handle actual comparisons between two values, but only after the comparison has been made. It was not designed to recognize when value comparisons are useful or required. Instead, the surrounding test code is responsible for making that evaluation, in this case, using a custom conditional statement not originating from any TSQLUnit function.

In addition, being accustomed to seeing in NUnit test messages that display the detailed results of value comparisons (i.e. expected against actual), print statements were added to the test procs to simulate that same text. For example, the following is what would be shown if the aforementioned test were to fail:

The product number is not the same.
Expected: 1234567890
Actual: 0987654321

Although a welcomed improvement in the feedback provided by the test runner's results, I found myself repeatedly injecting that same structure over and over in numerous other tests.

When this form of repetition occurs, a strategy can be adopted of either (1) continuing copying and pasting code, (2) using code generation, or (3) formulating and developing some reusable code component to manage the duplication. With # 1 and # 2 being obvious maintenance sinkholes draining away any value earned from the test code, implementing # 3 was a more sensible choice.

To fight off test code rot, the 'if' block was refactored and encapsulated into a separate, shareable stored procedure (think Extract Method). Nothing extravagant but quite effective:

create procedure dbo.tsux_AssertAreEqual
/* This is an extension to the TSQLUnit framework. */
(
  @expected        varchar(8000),
  @actual            varchar(8000),
  @failMessage    varchar(255)
)
as
begin
  if  @actual != @expected
      begin     
          exec tsu_failure @failMessage
          print 'Expected: '    + @expected
          print 'Actual: '    + @actual     
      end
end

Calling this new proc provides the familiar, sought-after "Assert.AreEqual" functionality found in NUnit and in a lot of other test frameworks. The old 'if' block in the original test was subsequently replaced with the new assert proc:

create procedure dbo.ut_CanUpdateProductNumber
/* UNIT TEST */
as
begin
  /*...unaltered code...*/
 
  /* ASSERT TEST */
  exec dbo.tsux_AssertAreEqual
      @expectedProductNumber,
       @actualProductNumber,
      'The product number is not the same.'
end

Now that we have a general utility assert sproc for the varchar type, we still have other data types, including int and datetime, that can also benefit from assertions of their own. Since TSQL does not support a flexible language feature like C# type generics for its stored procedures, creating sprocs for each data type is the only clear option to expand this functionality beyond vachar:

tsux_AssertDatesAreEqual
tsux_AssertIntsAreEqual

I can understand why the creator of TSQLUnit might have not initially built asserts into the framework since it requires building one for each every kind of data type in TSQL. Therefore, in its place, the burden falls on the user (i.e. me) to add additional asserts to the test code base as the need arises.

One kind of assert involving condition testing that might be interesting to implement but I am uncertain if it is remotely doable is this:

set @sqlConditionToEvaluate = (@expectedColor = 'BLUE'
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')
exec tsux_AssertIsTrue(@sqlConditionToEvaluate)

or more concisely:

exec tsux_AssertIsTrue(@expectedColor = 'BLUE'
AND @expectedSize = 23 OR StartDate between '1/1/11' and '2/2/22')

Maybe this could be achieved using dynamic sql and with storing each condition to verify within a 'TABLE' data type variable (functioning as an array) that can be looped checking each one to be true or false. However, implementing complex asserts to this extreme extent is a strong indication that TSQLUnit might no longer conceivably be the appropriate tool to write unit tests. It might be preferable to consider alternate unit testing frameworks that operate entirely outside of the database using a language other than TSQL that is better equipped for elaborate conditions and logic flow.