Writing Tests for a Stored Proc Sure Feels Weird

Some times you are just stuck and have to write some weird test fixtures to get the level of confidence you need to move forward in a legacy system. You can’t simply throw the baby out with the bath water no matter how much you really, really want every one to agree that is the best course of action.

In that situation, it is just as important to stick to your guns and find a way to wrap a test around what you are working on. Case in point, the current project I am on is your traditional business logic in sprocs application. By mandate, all updates to data must happen in a sproc so that business rules can be “enforced”.

The team of developers I have joined have no faith in Agile practices and see unit testing as a drain on their time and resources for no value. Interestingly enough, when I joined the team the vast majority of sprint items were bug fixes to multi-hundred line sprocs where the fix might actually cause more bugs. There was no real way to gain any kind of confidence other than poking the application with a stick.

Enter the stored procedure unit test fixture.

[TestFixture]
public class when_creating_a_new_research_item_and_an_open_research_item_already_exists : with_a_valid_security
{
    private Execute statement;

    public override void Because_of()
    {
        statement = Execute.Proceedure("spResearchItem_Create")
            .WithParameter("@TableName", tableName)
            .WithParameter("@ColumnName", columnName)
            .WithParameter("@AssignedToUser", user)
            .WithParameter("@ItemId", recordId)
            .WithParameter("@Note", note);
    }

    [Test]
    public void it_should_refuse_to_create_the_record()
    {
        Assert.Throws(() => { statement.AsNonQuery(); });
    }

    [Test]
    public void it_should_have_a_descriptive_error()
    {
        var error = Assert.Throws(() => { statement.AsNonQuery(); });

        error.Message.ShouldContain("Open Research Item Already Exists");
    }

    [Test]
    public void no_record_should_be_created()
    {
        var count =
            Execute.Statement(
                @"SELECT COUNT(*) FROM ResearchItem
                  WHERE TableName=@p1 AND ColumnName=@p2 AND SourceKeyVal1=@p3 AND IsOpen='Y'")
                .WithParameter("@p1", tableName)
                .WithParameter("@p2", columnName)
                .WithParameter("@p3", recordId)
                .AsValue();
        count.ShouldBe(1);
    }
}

This single fixture explicitly demonstrates a business rule, it can run with every build and we will get instant notification when this rule can be violated because of changes in the sproc. It is also nicely wrapped in a transaction that is automatically rolled back, so I can point it at any database and test its set of sprocs. It is not optimal, it is not pretty. But it does give you confidence to move forward.

Side Note: Don’t pay to much attention to the Execute class. It is simply a test helper to remove some of the tediousness of executing ADO code from the tests.