tSQLt FakeFunction: Mocking Scalar Functions in Your SQL Tests

Shannon Lowder

Shannon Lowder

— 2 min read

FakeTable handles table dependencies. SpyProcedure handles stored procedure dependencies. The gap that remained for a while was scalar functions: if your stored procedure called dbo.GetCurrentExchangeRate() or dbo.IsBusinessDay(@Date), you had no clean way to control what those functions returned during a test. The results depended on whatever was in the exchange rate table, or what day it happened to be when the test ran.

tSQLt's FakeFunction closes that gap.

How FakeFunction Works

FakeFunction swaps a scalar function with a replacement function you define. The replacement must have the same signature — same parameter names, types, and return type — but returns a value you control:

EXEC tSQLt.FakeFunction
    @FunctionName     = 'dbo.GetCurrentExchangeRate',
    @FakeFunctionName = 'Tests.FakeExchangeRate';

After this call, any code that calls dbo.GetCurrentExchangeRate within the test transaction hits your fake instead. The real function is restored when the transaction rolls back.

Defining the Fake

If the real function is:

CREATE FUNCTION dbo.GetCurrentExchangeRate(@CurrencyCode NVARCHAR(3))
RETURNS DECIMAL(10,6)
AS
BEGIN
    -- Production: queries a rate table updated by a daily job
    ...
END

Your fake needs matching parameters and return type:

CREATE FUNCTION Tests.FakeExchangeRate(@CurrencyCode NVARCHAR(3))
RETURNS DECIMAL(10,6)
AS
BEGIN
    RETURN 1.250000;  -- Fixed rate, fully deterministic
END

You can create multiple fakes for different scenarios — one that returns a high rate, one that returns zero, one for a specific edge case.

A Complete Test

Say you have a procedure that converts invoice amounts to USD before writing them to a reporting table:

CREATE PROCEDURE dbo.ProcessForeignInvoice
    @InvoiceID      INT,
    @OriginalAmount DECIMAL(10,2),
    @CurrencyCode   NVARCHAR(3)
AS
BEGIN
    DECLARE @USDAmount DECIMAL(10,2);
    SET @USDAmount = @OriginalAmount * dbo.GetCurrentExchangeRate(@CurrencyCode);

    INSERT INTO dbo.ProcessedInvoices (InvoiceID, USDAmount)
    VALUES (@InvoiceID, @USDAmount);
END
CREATE PROCEDURE InvoiceTests.[test ProcessForeignInvoice converts amount using exchange rate]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.ProcessedInvoices';
    EXEC tSQLt.FakeFunction
        @FunctionName     = 'dbo.GetCurrentExchangeRate',
        @FakeFunctionName = 'Tests.FakeExchangeRate';
    -- FakeExchangeRate returns 1.25 for any currency code

    -- Act
    EXEC dbo.ProcessForeignInvoice
        @InvoiceID      = 42,
        @OriginalAmount = 100.00,
        @CurrencyCode   = 'EUR';

    -- Assert: 100.00 * 1.25 = 125.00
    CREATE TABLE #Expected (InvoiceID INT, USDAmount DECIMAL(10,2));
    INSERT INTO #Expected VALUES (42, 125.00);

    CREATE TABLE #Actual (InvoiceID INT, USDAmount DECIMAL(10,2));
    INSERT INTO #Actual SELECT InvoiceID, USDAmount FROM dbo.ProcessedInvoices;

    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;

This test produces the same result regardless of what's in the exchange rate table, what time it runs, or which environment it's in. That's what deterministic means in practice.

Testing Edge Cases With Different Fakes

-- Zero exchange rate — should your procedure handle this gracefully?
CREATE FUNCTION Tests.FakeExchangeRateZero(@CurrencyCode NVARCHAR(3))
RETURNS DECIMAL(10,6) AS BEGIN RETURN 0.000000; END;

CREATE PROCEDURE InvoiceTests.[test ProcessForeignInvoice handles zero exchange rate]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.ProcessedInvoices';
    EXEC tSQLt.FakeFunction
        @FunctionName     = 'dbo.GetCurrentExchangeRate',
        @FakeFunctionName = 'Tests.FakeExchangeRateZero';

    EXEC dbo.ProcessForeignInvoice @InvoiceID = 1, @OriginalAmount = 100.00, @CurrencyCode = 'XYZ';

    DECLARE @Result DECIMAL(10,2);
    SELECT @Result = USDAmount FROM dbo.ProcessedInvoices WHERE InvoiceID = 1;
    EXEC tSQLt.AssertEquals 0.00, @Result;
END;

The Complete Isolation Toolkit

Between FakeTable, SpyProcedure, and FakeFunction, you can now isolate any SQL Server object from all of its dependencies. Tables, stored procedures, scalar functions — all substitutable with controlled fakes for the duration of a test. That's the same isolation model application developers have had with mocking frameworks for years, available in T-SQL. As always, I'm here to help.

Read more