Writing Tests for Stored Procedures

A procedure compiles after a change, then fails on the first unusual row. Tests for stored procedures make the expected result visible with arranged data, one call, and an assertion you can rerun before release.

Choose a Behavior to Prove

A useful test begins with a statement of behavior. For example, an order total procedure should ignore canceled lines and return zero for an empty order. That is more precise than saying the procedure should work. Write one test for each meaningful rule, including error handling and transaction effects. A test with no expected result is only a demonstration.

I start with the branch that caused the last confusing review, then add the normal path. Test names should say what is protected. Which input changes the result? Include that boundary. A procedure with five parameters can have far more behavior than one happy-path call reveals.

Arrange Data You Control

Use a disposable test database or a dedicated test schema when possible. Create only the rows needed to exercise the rule. Stable keys, explicit dates, and clear values make a failed assertion understandable. Do not rely on whatever data happens to be in a shared development database. A test should fail because code changed, not because somebody cleaned a sample table.

I wrap a bounded test in a transaction when the procedure does not commit independently. The transaction can be rolled back after the assertion. Verify the procedure’s transaction behavior first, because nested work, external calls, or side effects can make rollback insufficient. If an email is sent, a rollback cannot unsend it.

Act Through the Public Procedure

Execute the stored procedure through the same parameters and permissions the application uses. Avoid testing only a copied SELECT from its body. That bypasses parameter validation, SET options, error handling, and side effects. Capture output parameters, result sets, and changed rows according to the procedure contract.

The following pattern illustrates an output parameter test. It assumes dbo.GetOrderTotal exists and the test fixture includes OrderId 101 with a documented expected total. Replace the sample values with the fixture’s own values. Keep the procedure call inside a TRY block so the test rolls back on failure.

BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @ActualTotal decimal(18,2);
    EXEC dbo.GetOrderTotal
         @OrderId = 101,
         @Total = @ActualTotal OUTPUT;
    IF @ActualTotal IS DISTINCT FROM CONVERT(decimal(18,2), 25.00)
        THROW 51001, 'Unexpected order total.', 1;
    ROLLBACK TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Assert the Side Effects in Tests for Stored Procedures

An output value can be correct while the procedure updates the wrong rows. Inspect the affected table after the call. Assert both the intended change and the absence of an unintended one. If the procedure writes an audit record, check that record too. A test is strongest when it covers the observable contract rather than the implementation details.

I keep assertions small and fail with a message that names the rule. A SELECT that displays rows for a person to inspect is useful during development, but it is not an automated assertion. The second example checks that an order has exactly one final status row in a fixture. Adapt table names and values to the procedure under test.

IF (SELECT COUNT_BIG(*)
    FROM dbo.OrderStatus
    WHERE OrderId = 101 AND StatusCode = N'Approved') <> 1
    THROW 51002, 'Approved status was not recorded once.', 1;

Test Errors and Boundaries

Include missing keys, NULL inputs, duplicate business values, and values at maximum allowed length. Verify the error number or result the caller is promised. Do not assert the exact wording of every low-level SQL Server error unless that wording is part of the interface. A procedure should leave the transaction in a known state after failure.

I test a rollback path with data that would otherwise be modified. If a validation error occurs after one UPDATE but before another, the procedure should not leave half a business operation committed. The expected outcome belongs in the test. A neat success path is easy to write. The uncomfortable branch is the one that earns its maintenance cost.

Run Tests for Stored Procedures Before Every Change

Put the test scripts beside the procedure definition and make them part of the change routine. Run them against a fresh fixture before editing, after editing, and after deployment to a disposable environment. A test that fails before the edit is still useful evidence, but it cannot prove the new change caused the failure. Keep test data creation and cleanup repeatable.

Which tests for stored procedures cover the contract most likely to break? Add those first. I review a test when the business rule changes, not merely when the procedure text changes. A passing stale test can protect the wrong behavior with impressive consistency.

Keep Tests for Stored Procedures Maintainable

Avoid tests that depend on execution order or exact row identity values assigned by a shared database. Use stable business keys in the fixture and isolate each test’s changes. Split long scenarios into focused cases. When a setup step fails, report that failure directly instead of continuing into a misleading assertion.

I treat tests for stored procedures as code that needs review. Check that a test fails when the behavior is deliberately broken in a disposable copy. That simple challenge detects assertions that never inspect the real result. The goal is confidence in the procedure’s contract, not a growing count of green test names.

A useful stored-procedure test checks both the result and the side effects. After a success case, verify inserted or updated rows, output parameters and transaction state. Then force a controlled failure, such as an invalid input or a constraint violation, and confirm the procedure leaves no partial data. I name the expected error so a different failure cannot accidentally pass the test. An exception is only a correct outcome when it is the exception the test intended.

Keep test data isolated and easy to reset. A test that depends on yesterday’s identity value or a shared row can fail because of the environment rather than the procedure. I prefer fixed logical keys and a setup script that makes the preconditions obvious. A test suite is useful when a maintainer can read a failing assertion and understand which contract broke. If every failure says only that some count changed, the tests have become another puzzle to debug.

Related reading on this blog: Creating and Running an SQL Server Unit Test: Best Ways to Test SQL Queries and Testing Database Performance with tSQLt and SQLQueryStress.

A procedure test is not a sample EXEC, it is a repeatable claim about behavior.

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