Context

The arguments stated here specifically refer to the usage of stored procedures in an app development context, where they are used to encapsulate business logic inside the database. It does not refer to any other use cases. Moreover, the focus of this argument is SQL Server databases and ASP.NET codebases, where this pattern is most prevalent.

What are Stored Procedures

Stored Procedures encapsulate a series of SQL statements into a reusable, parameterised command stored in the database and called from application code. They work similarly to functions in traditional programming in that a stored procedure can be called from another, and include control flow and loops.

Arguments against Stored Procedures

Stored Procedures hide implementation details, and not in a good way Abstractions are good for consumers of APIs, but they’re not good when you’re inside the service layer itself and are trying to debug or extend it. In my experience, stored procedures have been the culprit behind numerous bugs in our systems, and this is mostly due to much of the implementation details being hidden behind the database. This is the main and by far the largest issue with stored procedures. Imagine you are a new backend developer onboarding onto the codebase, and you’re looking at a particular controller to see how it works, you go to the service that it’s calling, and see all the service is doing is making a call to the database. Even worse, you don’t even know what’s happening inside that call, all you know is the name of the procedure. Now you have two options, either give up and accept that part of the code as pure magic, or open an entirely new editor session and go to the database project (assuming you even have access to it in the first place). Creating an additional level of indirection during the development workflow increases the cognitive complexity of the program with little benefit. As a developer it’s slow to context switch between every route handler to go to the database.

Stored Procedures are hard to debug

Following from the previous point, debugging a stored procedure can feel like an impossible task, especially if it’s one that performs a destructive action like a write. If you make an edit, you have the option of running the tests for it (if any exist, more on this later) or just YOLO it and see if it works. In code, we usually have good tests that can verify functionality for us with little effort, but perhaps even more valuable, is the existence of a debugger. With a debugger we can jump exactly to the point in a LINQ query we’re interested in and see what’s happening to the data, change values, and see the results update. This makes debugging a breeze. Within the dark corners of a database, it starts to feel more like a ghost hunt.

Stored Procedures bypass version control and QA

Now let’s say our developer wants to make an edit to the stored procedure. The way version control works in the stored procedure is also painfully manual and dangerous. At any point anyone with write access can go and edit the stored procedure in any environment, causing immediate changes to take effect within the app with zero reviews. Suddenly errors are coming through, we look at the changelog of the backend service and see no changes. Stumped, we then look at the database to find a stored procedure has been edited multiple times without any documented changes. None of this would ever happen if the logic was placed in its rightful place, the service layer code. One might argue this is more of a governance problem, but the fact that this governance has to be disciplined into new developers is itself a disadvantage, whereas the whole team would get the quality assurance for free with an existing and well understood CI/CD pipeline.

Stored Procedures provide no type safety

The safety of a program is strongly informed by the safety of its type system. We use the type system to ensure we don’t accidently return a string when the client expects an integer. If we make such a mistake in code, our build fails, and the bug is fixed before it sees the light of production. In a stored procedure, we can return whatever we like, table, string, integer, or null. The first time we find out something is wrong is when we get an error coming in at runtime. This feedback loop is important. Increasing friction increases developer burn out.

Stored Procedures are legacy

The idea of storing business logic in the database was once deemed necessary because server infrastructure was still new, not fully understood, and sometimes slow. But today things are very different. Servers are fast, really fast, and sending a SQL query to the database via something like EF core has negligible performance differences compared to calling it directly in the database itself. Today, it is a well-respected and encouraged decision to include all the necessary business logic within the application code. EF core methods like ExecuteUpdate will batch multiple commands into one resulting in a single round trip, same as if it was a stored procedure. Moreover, ORMs like EF core will cache the compiled query, which the database further caches the execution plan for. Until a valid benchmark proves a stored procedure is more appropriate for latency, a code-first solution is the default.

Stored Procedures are hard to test

You may say stored procedures can be tested, using things like TSQL, and while I agree with that statement, the real question how easy it is to test them. The answer to that is really difficult. This is because by nature writing the test harness, arranging the tables and stubbing the records takes way too long. Were it in code, we get the benefits of all the existing testing infrastructure we have. Not to mention the tests would be noticeably faster as we don’t have to spin up an entire database every time. This enormous limitation means developers will naturally try to avoid writing tests for SQL queries if they can, and so we get fewer tests, or even worse, tests that don’t actually test anything meaningful, giving a false sense of security.

Counter Arguments

Calling SQL from code is slow

This is not true anymore. ORMs like EF core have come a long way and can do many things that were once the reason we had to resort to stored procedures. For example, we can now batch updates in a single LINQ query, avoiding network round trips. Moreover, EF core now has native JSON support, eliminating the need to resort to a stored procedure to use JSON utilities.

It’s easier to express some queries in raw SQL

I agree that there are extremely complex use cases out there that would be a pain to express in something like EF. However, in these extreme cases I would still rather fallback to something like Dapper to write the raw SQL in code. The goal is to always have all the necessary implementation details there in the code, without having to go to somewhere completely different. Though if the query is really that complex, it begs the question, should there be some intermediary transformation step before the data makes it to the database? Many complex SQL queries are due to a poor schema. Though I confess, for extremely niche use cases, such as elastic queries in Azure SQL executing in the database directly is the only way.

Stored Procedures enforce good security boundaries

A common pattern is to limit the server’s connection to the database to only have EXECUTE permissions, thus only calling the available stored procedures. In reality, this protection is not different to just giving the server connection read and write permissions. If the developer that writes the stored procedure is the same developer that writes the code, then it doesn’t matter if they write a destructive query via raw SQL or a LINQ query. Assuming existing authorization is implemented at the API level, if a bad actor gets root access to the API instance, well, we have bigger problems. Furthermore, for situations where you’d need air-tight security, there is the option to enable row-level security.

Final Words

Stored procedures are a painful construct that just make debugging harder, while providing little to no benefits. They may be an easy way to write a lot of business logic quickly, but ultimately this does not payoff in production.