Test-Driven Development in T-SQL: Writing the Test Before the Procedure

Shannon Lowder

Shannon Lowder

— 2 min read

Most developers learn unit testing as a retrofit: they have existing code, they write tests for it, and if they're diligent they maintain both going forward. Test-driven development flips that. You write the test first. The test fails because the code doesn't exist yet. Then you write the minimum code to make the test pass. Then you refactor.

That cycle — red, green, refactor — works in T-SQL just as well as it does in C# or Java. Here's what it looks like with tSQLt.

The Scenario

You need a stored procedure that calculates a late fee for overdue invoices: more than 30 days overdue gets a 5% fee, more than 60 days gets 10%, more than 90 days gets 15%. Minimum fee is $5.00.

Before writing a single line of implementation, write the tests.

Red: Write Failing Tests

EXEC tSQLt.NewTestClass 'InvoiceTests';
GO

CREATE PROCEDURE InvoiceTests.[test 35 days overdue applies 5 percent fee]
AS
BEGIN
    DECLARE @Fee DECIMAL(10,2);
    EXEC @Fee = dbo.CalculateLateFee @InvoiceAmount = 200.00, @DaysOverdue = 35;
    EXEC tSQLt.AssertEquals 10.00, @Fee;  -- 5% of 200
END;
GO

CREATE PROCEDURE InvoiceTests.[test 65 days overdue applies 10 percent fee]
AS
BEGIN
    DECLARE @Fee DECIMAL(10,2);
    EXEC @Fee = dbo.CalculateLateFee @InvoiceAmount = 200.00, @DaysOverdue = 65;
    EXEC tSQLt.AssertEquals 20.00, @Fee;  -- 10% of 200
END;
GO

CREATE PROCEDURE InvoiceTests.[test 95 days overdue applies 15 percent fee]
AS
BEGIN
    DECLARE @Fee DECIMAL(10,2);
    EXEC @Fee = dbo.CalculateLateFee @InvoiceAmount = 200.00, @DaysOverdue = 95;
    EXEC tSQLt.AssertEquals 30.00, @Fee;  -- 15% of 200
END;
GO

CREATE PROCEDURE InvoiceTests.[test minimum fee of 5 dollars applies on small invoice]
AS
BEGIN
    DECLARE @Fee DECIMAL(10,2);
    EXEC @Fee = dbo.CalculateLateFee @InvoiceAmount = 50.00, @DaysOverdue = 35;
    EXEC tSQLt.AssertEquals 5.00, @Fee;  -- 5% of $50 = $2.50, minimum kicks in
END;

Run them: EXEC tSQLt.RunTestClass 'InvoiceTests';

They all fail. dbo.CalculateLateFee doesn't exist. That's the red phase. You've defined exactly what the code needs to do before writing a character of implementation.

Green: Write the Minimum Implementation

CREATE PROCEDURE dbo.CalculateLateFee
    @InvoiceAmount DECIMAL(10,2),
    @DaysOverdue   INT
AS
BEGIN
    DECLARE @Rate DECIMAL(5,4);
    DECLARE @Fee  DECIMAL(10,2);

    SET @Rate = CASE
        WHEN @DaysOverdue > 90 THEN 0.15
        WHEN @DaysOverdue > 60 THEN 0.10
        WHEN @DaysOverdue > 30 THEN 0.05
        ELSE                        0.00
    END;

    SET @Fee = @InvoiceAmount * @Rate;

    IF @Fee < 5.00 AND @DaysOverdue > 30
        SET @Fee = 5.00;

    RETURN @Fee;
END;

Run the tests again. Green. All four pass.

Refactor: Change the Implementation Without Breaking Behavior

Now you can restructure the implementation safely. Maybe the rate logic belongs in a separate function for reuse. Maybe you want to add error handling for null inputs. Make the change, run the tests, verify nothing broke.

That last step is where TDD pays back its initial investment. Refactoring production SQL without tests is guesswork. With a test suite, you make a change and the tests tell you immediately whether the behavior is still correct.

What TDD Changes in Practice

Writing tests first forces you to think about the interface before the implementation — how the procedure should be called, what it returns, what the edge cases are. You make those decisions explicitly, in code, before writing any logic. The alternative is making those decisions implicitly while writing implementation and hoping you covered everything.

It takes adjustment. The first few times you write a failing test before touching the procedure, it feels backward. Give it three or four features and it starts to feel backward the other way — like implementation-first is driving with your eyes closed.

Between FakeTable, assertion procedures, and TDD, you now have a complete tSQLt workflow. Next year I'll cover SpyProcedure for mocking stored procedure dependencies. As always, I'm here to help.

Read more