Can you unit test a stored procedure?

Not in the strict sense, because a test of a stored procedure needs a running database, which makes it an integration test. Run that test on the production engine in a container, with each test inserting only the rows its case needs.

How do I test stored procedures in SQL Server?

Test SQL Server stored procedures with tSQLt on the SQL Server version production runs. Each test replaces the tables the procedure touches with empty copies without constraints, inserts the rows its case needs, runs the procedure and compares tables with tSQLt.AssertEqualsTable.

Should I test stored procedures from application code or in SQL?

Tests of stored procedures work from application code and in SQL, as long as they run the procedure on the production engine. Pick the language most of the team reads: tSQLt or pgTAP for teams that write SQL daily, the application's test framework for teams that mostly write C#, Java or Python.

Should I test stored procedures?

Verdict

Yes

Test each stored procedure that holds business rules with a database test on the production engine, started in a container, using rows that each branch and filter of the procedure must treat differently.

Why

Blast radiususers
Change frequencyregularly
Detectabilityeventually
Reversibilitywith-effort
Test costmoderate

Test every stored procedure that holds business rules, and run the tests on the database engine production uses. The typical case is a SQL Server or PostgreSQL backend whose application calls procedures that select, update and assign rows. Blast radius is users and Change frequency is regularly, because customers see these rows and features change the procedures monthly. Detectability is eventually, because a wrong branch in T-SQL or PL/pgSQL saves plausible rows without an error. Reversibility is with-effort, because the wrong rows need a repair script, and Test cost is moderate, because a test needs a database in a container, so rule R11 gives Test.

When the decision changes
When	Decision	Why
A stored procedure computes the invoice lines that the monthly billing job charges	Test mandatory: the main path, every known failure and the rounding and date boundaries, with a second person reviewing the tests	Blast radius rises to money and Reversibility to costly, because a wrong charge needs refunds
Several companies share one database, and each stored procedure filters rows by a tenant ID parameter	Test mandatory: rows of two tenants in each test, asserting that the procedure returns or changes one tenant's rows	Blast radius rises to safety-or-legal and Detectability to never, because a missing tenant filter leaks records without an error
The stored procedure runs one SELECT by primary key, and endpoint tests call it on a real database with a distinct value in each returned column	Do not test the procedure on its own; the endpoint tests cover it	Detectability moves to immediately and Reversibility to trivial, because a lookup stores nothing and a wrong column fails those tests in CI
The stored procedures read other databases through linked servers, so tests can run them only on a shared staging server	Test it differently: a nightly job compares each procedure's output with a reference query and alerts on a difference	Test cost rises to heavy while Detectability stays eventually and Reversibility with-effort
The stored procedure is a one-time backfill that rewrites existing rows after a schema change	Test it differently: run it on a restored copy of production, compare row counts and sample rows, and keep a backup	Change frequency falls to once while Blast radius stays users

What breaks if you don't test

A stored procedure with branches, cursor loops and variable assignments fails the way application code fails, but the application's suite never runs it. Tests of the calling code mock the data access class and stay green when the procedure is wrong. A wrong branch assigns, hides or updates rows with no error, and a customer notices weeks later that a record is missing or misplaced.

What you lose if you over-test

One over-test mocks the connection and asserts that EXEC dbo.AssignNewTickets was sent; it passes when the procedure is wrong, because the procedure never runs. A second over-test fakes every table in the schema, including lookup tables the procedure only reads, so each test breaks on unrelated schema changes.

How to test

Write database tests on the production engine and major version, started in a container for each CI run; SQL Server ships Linux container images. For SQL Server, a tSQLt test fakes the tables the procedure touches, runs it, compares tables and rolls back. For PostgreSQL, pgTAP compares a function's rows with expected rows through results_eq(). The minimum set:

One test per branch, with rows that each branch treats differently.
One test per filter, with rows it must return and rows it must leave out.
One test where a lookup inside the procedure finds no row.

Procedure and references

When the answer changes

The procedure computes a charge, a payout or a price.
The procedure filters rows by tenant or owner.
The procedure runs one time, as a backfill or a migration.

Real incident + Code example

The tickets that went to the wrong agent

On a helpdesk product I worked on, the SQL Server procedure dbo.AssignNewTickets looped over new tickets with a cursor and looked up each account's owner with SELECT @OwnerId = a.OwnerId. A change to skip owners who had left the company joined that lookup to dbo.Agents with IsActive = 1. When a SELECT assignment returns no rows, the variable keeps its current value, so a ticket whose account had no active owner went to the previous ticket's owner instead of the unassigned queue. The C# tests mocked the repository and passed. Twelve days later a customer escalated an unanswered ticket, and we reassigned about 180 tickets by script. The fix used SET @OwnerId = (SELECT ...), which assigns NULL when nothing matches, plus this tSQLt test:

EXEC tSQLt.NewTestClass 'AssignTickets';
GO
CREATE PROCEDURE AssignTickets.[test ticket of an account without an active owner stays unassigned]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Agents';
    EXEC tSQLt.FakeTable 'dbo.Accounts';
    EXEC tSQLt.FakeTable 'dbo.Tickets';
    INSERT dbo.Agents (AgentId, IsActive) VALUES (7, 1), (8, 0);
    INSERT dbo.Accounts (AccountId, OwnerId) VALUES (1, 7), (2, 8);
    INSERT dbo.Tickets (TicketId, AccountId, AssigneeId) VALUES (101, 1, NULL), (102, 2, NULL);

    EXEC dbo.AssignNewTickets;

    SELECT TicketId, AssigneeId INTO #Expected
    FROM (VALUES (101, 7), (102, NULL)) AS e (TicketId, AssigneeId);
    SELECT TicketId, AssigneeId INTO #Actual FROM dbo.Tickets;
    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;

Sources

FAQ

Can you unit test a stored procedure?: Not in the strict sense, because a test of a stored procedure needs a running database, which makes it an integration test. Run that test on the production engine in a container, with each test inserting only the rows its case needs.
How do I test stored procedures in SQL Server?: Test SQL Server stored procedures with tSQLt on the SQL Server version production runs. Each test replaces the tables the procedure touches with empty copies without constraints, inserts the rows its case needs, runs the procedure and compares tables with tSQLt.AssertEqualsTable.
Should I test stored procedures from application code or in SQL?: Tests of stored procedures work from application code and in SQL, as long as they run the procedure on the production engine. Pick the language most of the team reads: tSQLt or pgTAP for teams that write SQL daily, the application's test framework for teams that mostly write C#, Java or Python.