Any solutions to getting Nunit style TestCase() functionality in tSQLt?

Question

I am currently looking to write some unit tests for some SQL Server SQL code using tSQLt.

After reading the docs it appears that there is no support for being able to call a test case with parameters, instead of writing separate test cases for each parameter combination. (For the sake of this question please put aside whether you think it is a good idea to pass parameters to a test case vs writing a separate one for each parameter combination)

For instance in NUnit you can do something as follows using the TestCase attribute:

[TestCase("", -1, false)]
[TestCase("ACT ", 1, true)]
[TestCase("ACT", 1, true)]
[TestCase("aCT", 1, true)]
[TestCase("AUSTRALIAN CAPITAL TERRITORY", 1, true)]
[Test]
public void DetermineStateIdFromText(string aStateText, long aExpectedStateId, bool aExpectedFound)
{
    //Arrange
    WzDetermineStateIdFromTextInput input = new WzDetermineStateIdFromTextInput
                                            {
                                                StateText = aStateText
                                            };

    //Act
    WzDetermineStateIdFromTextOutput output = _WzLocationMappingService.DetermineStateIdFromText(input);

    //Assert
    output.ResultSuccess.ShouldBeTrue();
    output.Found.ShouldBe(aExpectedFound);
    if (output.Found)
    {
        output.StateId.ShouldBe(aExpectedStateId);
    }
}

(The guts of the test method are irrelevant and are included just for completeness. The ShouldBe() calls are from Shouldly in case you are wondering where the Asserts are.)

Has anyone got any solutions to providing TestCase style support within tSQLt?

Answer 1

tSQLt allows the use of stored procedures that are not test cases within test classes. So to achieve, what you are looking for, I usually create a stored procedure that accepts parameters and handles all the test logic. Then I write a bunch of single line test procedures that call the other procedure passing in the appropriate parameters. There are a few examples for this in the test cases for tSQLt itself.

This approach has very few disadvantages over truly parameterized tests, but one big advantage: You can give each case a truly meaningful name that will make hunting down issues a lot simpler. (True parameterized tests are on the backlog, but they are not the highest priority, so it might be a while for them to make it.)

As a side note, I strongly advise against autogenerating tests or even parameters for tests, as that usually leads to higher maintenance costs. As the goal of testing is to reduce the cost of maintaining your codebase, that is counterproductive. I've seen a lot of unit testing adoption projects fail because of that very reason.