Given-When-Then for Database Tests

Read a database test and look for the reason it would fail. Given-When-Then makes that story visible in three small parts.

Give Each Given-When-Then Test One Story

Given establishes a known starting state. When performs the operation under test. Then checks the promised result with executable assertions.

I use those labels when reviewing database behavior with another developer. I also keep the setup deliberately smaller than production data. Large fixtures hide the reason a test exists.

A comment saying that a query looks correct is not an assertion. The script must fail when the result is wrong. THROW gives that failure a clear error number and message.

This example tests one stock reduction procedure in three ways. A valid request reduces stock, excess demand fails, and an invalid quantity fails. Each test rolls its fixture back afterward.

Define the Procedure Contract First

Create these objects in a fresh, isolated test database. The table constraint prevents negative stored quantities. The procedure rejects NULL or nonpositive requested quantities before attempting an update.

The stock update uses one conditional statement. It succeeds only when the product exists with enough stock. Missing products and insufficient stock intentionally share the same error contract here.

CREATE TABLE dbo.TestStock
(
    ProductId int NOT NULL PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity >= 0)
);
GO
CREATE OR ALTER PROCEDURE dbo.TakeStock
    @ProductId int,
    @Quantity int
AS
BEGIN
    SET NOCOUNT ON;
    IF @Quantity IS NULL OR @Quantity <= 0
        THROW 50001, 'Requested quantity must be positive.', 1;
    UPDATE dbo.TestStock
    SET Quantity = Quantity - @Quantity
    WHERE ProductId = @ProductId AND Quantity >= @Quantity;
    IF @@ROWCOUNT <> 1
        THROW 50002, 'Product unavailable or stock insufficient.', 1;
END;
GO

Capture or inspect @@ROWCOUNT immediately after the relevant statement. Intervening statements can change it. The procedure checks it directly after UPDATE.

This procedure does not commit the caller's transaction. The test owns setup and rollback. A procedure that commits unrelated caller work violates a different contract requiring separate review.

Test a Successful Reduction

The first case starts with ten units and requests three. Those are chosen fixture values, not reported measurements. The assertion requires the exact expected row to exist.

A scalar comparison can accidentally accept a missing row through NULL behavior. NOT EXISTS expresses the failure condition clearly. A separate row count assertion detects unexpected additional fixture rows.

SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50100, 'Start these tests outside a transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    -- Given
    INSERT dbo.TestStock(ProductId, Quantity) VALUES (1, 10);
    -- When
    EXEC dbo.TakeStock @ProductId = 1, @Quantity = 3;
    -- Then
    IF NOT EXISTS
    (
        SELECT 1 FROM dbo.TestStock
        WHERE ProductId = 1 AND Quantity = 7
    )
        THROW 50101, 'Expected product 1 to have seven units.', 1;
    IF (SELECT COUNT_BIG(*) FROM dbo.TestStock) <> 1
        THROW 50102, 'Unexpected stock rows appeared.', 1;
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Run these examples against an otherwise empty demonstration table. Existing rows make the total row count assertion unsuitable. The explicit fixture requirement prevents accidentally testing an unknown database state.

The successful test ends without retaining the ten-unit setup row. It also removes the reduction performed inside that transaction. A passed test does not need to leave its evidence in business tables.

Write Given-When-Then for Insufficient Stock

Given-When-Then also describes expected failures. The second case requests eleven units from a ten-unit fixture. Its success condition is the documented error plus unchanged stock.

An expected error needs careful handling. Catch the specific error number and rethrow any other error. A connection or permission failure is not proof that stock protection worked.

SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50100, 'Start these tests outside a transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    -- Given
    INSERT dbo.TestStock(ProductId, Quantity) VALUES (1, 10);
    DECLARE @ExpectedError bit = 0;
    -- When
    BEGIN TRY
        EXEC dbo.TakeStock @ProductId = 1, @Quantity = 11;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 50002 THROW;
        SET @ExpectedError = 1;
    END CATCH;
    -- Then
    IF @ExpectedError = 0
        THROW 50103, 'Expected an insufficient-stock error.', 1;
    IF NOT EXISTS
    (
        SELECT 1 FROM dbo.TestStock
        WHERE ProductId = 1 AND Quantity = 10
    )
        THROW 50104, 'Failed request changed the stock row.', 1;
    IF (SELECT COUNT_BIG(*) FROM dbo.TestStock) <> 1
        THROW 50105, 'Failed request changed fixture membership.', 1;
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

THROW honors XACT_ABORT. An expected failure can leave an active transaction uncommittable. Reads and a full rollback remain allowed in that state.

The assertions after the expected error only read table data. They perform no further database writes. The test then rolls back regardless of whether the transaction remains committable.

Test Invalid Input Before Mutation

The third case requests zero units. It expects the input-validation error rather than the stock-availability error. That distinction documents where the procedure rejects the request.

Keep this fixture independent from the previous test. Sharing the first test's remaining row would create an order dependency. Independent setup lets you run this case on its own.

SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50100, 'Start these tests outside a transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    -- Given
    INSERT dbo.TestStock(ProductId, Quantity) VALUES (1, 10);
    DECLARE @ExpectedError bit = 0;
    -- When
    BEGIN TRY
        EXEC dbo.TakeStock @ProductId = 1, @Quantity = 0;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 50001 THROW;
        SET @ExpectedError = 1;
    END CATCH;
    -- Then
    IF @ExpectedError = 0
        THROW 50106, 'Expected an invalid-quantity error.', 1;
    IF NOT EXISTS
    (
        SELECT 1 FROM dbo.TestStock
        WHERE ProductId = 1 AND Quantity = 10
    )
        THROW 50107, 'Invalid input changed stock.', 1;
    IF (SELECT COUNT_BIG(*) FROM dbo.TestStock) <> 1
        THROW 50108, 'Invalid input changed fixture membership.', 1;
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Extend this pattern for NULL quantities and missing products. Write separate cases when the expected behavior deserves its own explanation. One enormous test makes a failure harder to locate.

All three scripts passed on my SQL Server 2025 test database and left the table empty. Execute them in your own isolated database before adopting the pattern. Review every failure message against the intended procedure contract.

Know What Rollback Cannot Clean

A database transaction does not undo every possible side effect. Identity values and sequence consumption can advance despite rollback. External messages and files require their own isolation or test substitutes.

A concurrent session can also observe behavior outside the fixture transaction. Use a dedicated database and controlled connections for these examples. Concurrency guarantees need additional coordinated tests with more than one session.

What should the next developer learn from a failed assertion? Include the expected business state in its message. An error saying only that something failed contributes very little.

I keep Given-When-Then comments close to their executable statements. I remove setup details that do not influence the assertion. A test fixture should not need its own archaeology department.

Review Given-When-Then Assertions as Carefully as the Procedure

Given-When-Then should remain readable when the procedure implementation changes. Assert the public result rather than the internal statement sequence. A correct implementation can use another query shape while honoring the same business contract.

For result sets, compare the complete intended row set when membership matters. Checking only a total can miss substituted rows or duplicate entries. Use explicit keys and value comparisons so equal counts do not hide unequal results.

Be deliberate about NULL in those comparisons. Ordinary equality does not treat two NULL values as equal. Handle nullable output columns explicitly when defining the expected relationship between actual and expected rows.

The stock example avoids that ambiguity by using a nonnullable quantity column. Its existence assertion checks both the product identifier and quantity together. A missing row therefore fails even when a scalar subquery would return NULL.

Expected failures deserve unchanged-state assertions beyond the immediate target when necessary. A procedure can reject stock reduction after writing an unrelated audit row. Decide which persistent side effects belong to the public contract and test those tables too.

Keep error numbers stable when callers depend on them. The sample separates input errors from availability errors intentionally. Changing message wording is different from changing an error code that an application uses for decisions.

A failed assertion should roll back before handing the error to another caller. The outer CATCH performs that cleanup in every script. It also rethrows the original failure so an automated runner can classify the case correctly.

Finally, remove the procedure's protection temporarily in an isolated copy to challenge the test. A meaningful test should fail when the behavior it protects disappears. This small mutation check confirms that the assertion can detect the intended defect.

Related reading on this blog: Gherkin Language: A Key to Testing Across Multiple Languages and Creating and Running an SQL Server Unit Test: Best Ways to Test SQL Queries.

A database test is not a query that ran, it is a contract that can fail clearly.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.