tSQLt SpyProcedure: Mocking Your Stored Procedure Dependencies

Shannon Lowder

Shannon Lowder

— 2 min read

The hardest tests to write aren't the ones that verify simple logic — those are manageable with FakeTable and AssertEquals. The hard ones are tests for stored procedures that call other stored procedures. You're trying to test Procedure A, but Procedure A calls Procedure B, which sends an email, or writes to an audit log, or calls a third-party API through a CLR procedure. You don't want any of that happening during a unit test.

tSQLt's answer is SpyProcedure.

What SpyProcedure Does

SpyProcedure replaces a stored procedure with a stub that does nothing except log the fact that it was called and with what parameters. The original procedure is preserved and restored when the test transaction rolls back.

EXEC tSQLt.SpyProcedure 'dbo.SendEmailNotification';

After this call, any code that calls dbo.SendEmailNotification within the test transaction hits the spy version instead. No emails go out. tSQLt records every call — which procedure was invoked, when, and with what parameters — in a log table you can query to assert behavior.

A Real Example

Say you have a procedure that updates an order status and sends a notification when the order ships:

CREATE PROCEDURE dbo.UpdateOrderStatus
    @OrderID   INT,
    @NewStatus NVARCHAR(20)
AS
BEGIN
    UPDATE dbo.Orders
    SET Status = @NewStatus, UpdatedAt = GETDATE()
    WHERE OrderID = @OrderID;

    IF @NewStatus = 'Shipped'
    BEGIN
        DECLARE @CustomerEmail NVARCHAR(200);
        SELECT @CustomerEmail = c.Email
        FROM dbo.Customers c
        JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
        WHERE o.OrderID = @OrderID;

        EXEC dbo.SendEmailNotification
            @ToAddress = @CustomerEmail,
            @Subject   = 'Your order has shipped';
    END
END

Here's the test verifying that a notification fires exactly once when the status is 'Shipped':

CREATE PROCEDURE OrderTests.[test UpdateOrderStatus sends notification on shipped]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.Orders';
    EXEC tSQLt.FakeTable 'dbo.Customers';
    EXEC tSQLt.SpyProcedure 'dbo.SendEmailNotification';

    INSERT INTO dbo.Customers (CustomerID, Email) VALUES (101, 'luke@rebelalliance.org');
    INSERT INTO dbo.Orders (OrderID, CustomerID, Status) VALUES (1, 101, 'Pending');

    -- Act
    EXEC dbo.UpdateOrderStatus @OrderID = 1, @NewStatus = 'Shipped';

    -- Assert: notification was called once with the right address
    DECLARE @CallCount INT;
    SELECT @CallCount = COUNT(*)
    FROM dbo.SendEmailNotification_SpyProcedureLog
    WHERE _Params LIKE '%luke@rebelalliance.org%';

    EXEC tSQLt.AssertEquals 1, @CallCount;
END;

tSQLt automatically creates ProcedureName_SpyProcedureLog. It captures parameter values in an XML-based _Params column, which you can search to assert what the procedure was called with.

Asserting That a Procedure Was NOT Called

The inverse is equally useful. For a non-Shipped status update, no notification should fire:

CREATE PROCEDURE OrderTests.[test UpdateOrderStatus does not notify on non-shipped status]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Orders';
    EXEC tSQLt.FakeTable 'dbo.Customers';
    EXEC tSQLt.SpyProcedure 'dbo.SendEmailNotification';

    INSERT INTO dbo.Customers (CustomerID, Email) VALUES (101, 'han@millenniumfalcon.net');
    INSERT INTO dbo.Orders (OrderID, CustomerID, Status) VALUES (1, 101, 'Pending');

    EXEC dbo.UpdateOrderStatus @OrderID = 1, @NewStatus = 'Processing';

    EXEC tSQLt.AssertEmptyTable 'dbo.SendEmailNotification_SpyProcedureLog';
END;

SpyProcedure With a Controlled Return Value

When the dependency you're spying on returns a value your procedure uses, you can control what the spy returns via @CommandToExecute:

EXEC tSQLt.SpyProcedure 'dbo.GetInventoryLevel',
    @CommandToExecute = 'SET @QuantityAvailable = 100;';

This lets you test different code paths by controlling what the dependency "returns" — without running the actual dependency at all.

The Pattern This Enables

FakeTable handles data dependencies. SpyProcedure handles procedural dependencies. Together they let you test any stored procedure in complete isolation — regardless of how tangled its dependencies are in production. Next up: AssertEqualsTable and the right way to verify complex result sets. As always, I'm here to help.

Read more