I'll just come out and say it: I hate stored procedures.

I've been thinking about this post for a cool 10 years now.

Now of course, I have to make the standard disclaimer "not all stored procedures." But I've been wanting to shout from the rooftops for YEARS that in modern software development, there are very few reasons to reach for stored procs.

Know what? It might not even be the stored procedures themselves that drive me crazy. What gets me is that there are so many devs out there that just don't think about WHY they think stored procedures are so great.

My top reasons that stored procs are (usually) a mistake:

• Out of date thinking on performance
• Kinda pointless security concerns
• Just another thing to deploy — they complicate your CI/CD pipelines
• They're a "dust magnet" for business logic that has no business being in the database
• Business logic in stored procs is like an invasive plant that turns into code duplication
• They're almost impossible to unit test

And the common excuse I hear that makes me roll my eyes the hardest: "The SQL that EF Core generates is ugly." Ugly SQL is a weird complaint — who cares? SQL that gives you bad performance? Then sure, go for a stored proc. But that's probably a rare case. Don't start there. Document real performance problems and fix them. Don't pre-optimize.

Now — stored procs DO have their place. Two really solid use cases:

• Set-based operations — bulk imports, aggregation queries, data transformations. The database is good at that. Let it do its job. (Reporting queries? I could be convinced. Maybe.)
• Genuine, documented performance problems where you've proven that a hand-tuned query makes a measurable difference.

But using stored procedures as your application programming model? Putting your business logic, your validation, your workflow orchestration into T-SQL? Writing a stored proc for your CRUD operations? That's what I want to talk about.

Let Me Scope This

I'm an enterprise software architect and developer. I specialize in .NET and Azure. I build and rescue line-of-business applications — the kind with web APIs, service layers, domain logic, and databases behind them. That's my world, and that's the world I'm talking about.

I'm not talking about data warehouses. I'm not talking about ETL pipelines. I'm not talking about reporting. If you're a DBA writing set-based transformations inside SQL Server, this post is not aimed at you. We're cool.

I'm talking about application developers who write stored procedures for their CRUD operations and their business logic. And let me knock out the CRUD thing first because it's the easiest one.

CRUD Stored Procs != Performance Gains

(BTW, CRUD is an abbreviation for Create, Read, Update, Delete.)

This is the one that drives me crazy because I hear it all the time and it's wildly out-of-date.

Here's the out-of-date wisdom: "Using stored procedures instead of dynamic SQL gives you a performance boost because SQL Server doesn't have to recompile the query execution plan."

My theory for why this myth just won't die is that it used to be correct. In early versions of SQL Server, it couldn't and didn't cache query plans for dynamic SQL. So if you gave it the exact same SQL query -- for example, select * from person, it would regenerate the query execution plan every time.

This hasn't been true for 20-ish years.

You almost certainly don't need stored procedures for CRUD operations anymore.

Starting with SQL Server 2005, and especially with the "optimize for ad hoc workloads" setting in 2008, the query optimizer got dramatically better at caching execution plans for parameterized ad hoc queries. EF Core generates parameterized queries. Those queries get cached execution plans just like stored procedures do. The performance difference for basic create, read, update, delete operations is effectively zero.

If you're writing stored procedures to wrap your INSERT, UPDATE, and DELETE statements in 2026, you're adding complexity for no benefit. EF Core handles this. It's been handling this for years. You can stop.

The real problem — and the thing I've been biting my tongue about for a decade — is business logic in stored procedures. And to understand why that's so pervasive, you have to understand where it came from.

Where the Mindset Came From

The "put your business logic in stored procedures" approach has deep roots, and honestly? In the world it came from, it made sense.

Oracle made its name in the client-server era. PL/SQL was a legitimate programming language — packages, types, exception handling, the works. Oracle Forms gave you a UI that ran directly against the database. The entire Oracle developer identity was built around the database being the center of the universe, and the application was a thin presentation layer on top.

In that world, stored procedures as your primary code organization principle were totally reasonable. If the application is Oracle Forms talking to Oracle, then why wouldn't you put your logic in PL/SQL? That's where the power was. That's where the tools were. The database wasn't just storing your data — it was running your application.

But then the internet happened around 1995 or so and the app dev world started to change. Plus Microsoft released SQL Server right around that time along with Active Server Pages.

When Oracle developers migrated to SQL Server in the late 90s and early 2000s (and a lot of them did — cost, the Microsoft enterprise push, the Windows ecosystem), they brought that architecture with them. Business logic in procs, the database as the system of record for behavior not just data. T-SQL wasn't as capable as PL/SQL, but the pattern was the same.

And Microsoft's own guidance reinforced it. On SQL Server 6.5 and 7.0, the performance arguments were real. The security arguments were real — granting EXECUTE on a procedure was simpler than table-level permissions. The books said stored procs were best practice. The conference talks said it. Senior devs taught it to junior devs. (♫ "...it's the cirrrrrcle of life..."♫)

But there was a crucial difference that got lost in the migration. The Microsoft developer world grew up building internet applications. Web apps. Multi-tier architectures. The application server became the center of gravity, not the database. You weren't hosting forms, you were serving up text in the form of HTML. The client wasn't Oracle Forms sitting on a desktop talking directly to the database — it was a browser talking to IIS talking to ASP.NET talking to SQL Server. There were layers. There were boundaries. The architecture was fundamentally different.

The stored procedure habit came along anyway.

And then two things happened that made the habit indefensible — but the guidance never caught up.

"But Performance!"

I already covered the query plan caching story above, but there's a bigger picture here.

The .NET ecosystem matured into a genuinely great home for business logic. C# became a real enterprise language. Dependency injection became standard practice. Interface-based programming gave us real abstractions. Testing frameworks made testability a first-class concern. Refactoring tools got amazing. Eventually containers become a thing. Suddenly there was a dramatically better place to put your business logic — a place with compile-time type checking, real debugging with actual breakpoints, unit testability, and refactoring tool support.

Stored procedures have nothing comparable^*. You're writing business logic in a language that was designed for set-based data operations, debugging it with PRINT statements and SSMS, and testing it by... well, mostly you aren't testing it.

The guidance never got updated. The books never got revised. The conference talks kept saying "stored procs for performance and security" without ever stating or examining 'why'. The senior devs who learned it that way taught the next generation, who taught the next generation. And now, in 2026, I walk into client engagements and find business logic entombed in T-SQL procedures that nobody can test, nobody can refactor, and nobody fully understands — because the developer who wrote them left the company in 2014.

[* - yes, i know about SSDT and Red Gate's tools.]

"But Security!"

There's a variation of the stored procedure argument that goes like this: "We use stored procedures as an abstraction layer to protect the data. Applications don't touch tables directly — they go through procs."

This feels like good architecture. It sounds like encapsulation. But it's kind of a trap.

What you've actually created is a tightly coupled contract between your application and your database that's harder to version, harder to test, harder to refactor, and harder to deploy than an actual application-level abstraction. You've replaced a real abstraction — an interface, a repository, a service — with a fake one that lives in the wrong layer.

An interface in C# gives you compile-time checking, IDE navigation, refactoring support, mockability for testing, and the ability to swap implementations without touching the consumer. A stored procedure gives you... a string-based contract that fails at runtime and can't be mocked without a database.

That's not an abstraction layer. That's a liability.

Just Another Thing to Deploy & Version

This one doesn't get enough attention.

Stored procedures live in the database. Your application code lives in your repo. These are two separate deployment pipelines, two separate versioning stories, and two separate sets of "did we remember to update that?"

When your business logic is in C# behind interfaces, it deploys with your application. One build. One artifact. One pipeline. You version it with Git. You review it in a pull request. You roll it back by redeploying the previous build. Done.

When your business logic is split between application code and stored procedures, every deployment is a coordination exercise. Did the new app code go out? Did the matching stored procedure changes get applied? In the right order? To the right environment? What happens if the app deploys but the database update fails? Now you've got a version mismatch in production and you're scrambling.

And let's be honest about the tooling gap. Your C# code has a mature CI/CD story — build, test, deploy, rollback. Database deployments are still scarier for most teams. A lot of organizations I work with still have stored procedure changes that get applied manually by a DBA. Some of them aren't even in version control. (I know. I KNOW.)

Every stored procedure you add is another artifact that has to be versioned, deployed, tested, and coordinated separately from your application. In a world where CI/CD is supposed to make deployments boring and routine, stored procedures are just another thing to keep deploys "exciting". And exciting deployments are bad deployments.

The Dust Magnet

Here's what actually happens when you have a stored procedure layer: business logic gravitates toward it.

It starts innocently. Someone needs to validate an input before inserting a row. "I'll just add a check in the proc." Then someone adds a business rule. Then a calculation. Then a workflow step. Each addition makes sense in isolation — the data is right there, the proc is "closer to the data," it's "more efficient."

(Then there's the problem with the rogue developer or DBA who likes to make stored proc changes on the fly in production without going through the CI/CD process.)

Before you know it, you've collapsed three architectural layers into one. The database is simultaneously the persistence layer, the data access layer, and the business logic layer. Everything welded together. No seams. No contracts. No ability to swap one piece without touching everything else. No obvious place to find that logic when you're debugging or need to make a change.

In a well-designed application, you've got distinct layers: your domain and business logic, your data access abstraction (an interface — a contract), your data access implementation (EF Core or whatever), and then the database itself. The database stores data and enforces data integrity constraints — NOT NULL, UNIQUE, foreign keys — but it contains zero business logic. Each layer has a job. Each layer is replaceable. Each layer is testable.

Stored procedures destroy that separation. They're a dust magnet for logic that belongs in C# — and once it's in there, it's really hard to get it out.

The Invasive Plant

This one's related to the dust magnet problem, but it's worse.

Once business logic lives in stored procedures, your business rules end up living in two places: the application code and the database. The proc validates the input. The C# also validates the input. The proc enforces a business rule. The service layer also enforces that rule. Maybe slightly differently. Maybe not — but who's checking?

You can't refactor the business logic without coordinating database changes. You can't understand the full behavior of the system without reading both C# and T-SQL. You can't confidently answer the question "where does this rule live?" because the honest answer is "probably both places, but we're not sure they agree."

Or what about if that business rule is used by more than one stored proc? Or maybe a stored proc and a function and a trigger? "Ehhhhh just make a copy of that logic. I'm sure it'll be fine."

It's like an invasive plant. It starts in one place, sends runners everywhere, and before long it's so deeply rooted in the system that removing it would mean tearing up the whole garden. The duplication isn't a bug — it's a survival mechanism. Teams duplicate logic into the application because they can't trust (or can't easily call) what's in the proc, but they also can't remove the proc because something else might depend on it.

That's not separation of concerns. That's having the same concern in two places and hoping they stay in sync.

The Testability Problem (This Is the Big One)

This is where I get the angriest. I care deeply about testability, and stored procedures are one of the hardest things to unit test in the entire Microsoft ecosystem.

Want to test the business logic in a stored procedure? You need a running database. You need test data set up correctly. You need to manage state between tests. You need to clean up after yourself. The tests are slow, brittle, and order-dependent. They require infrastructure. They're hard to consistently run on a developer's laptop. It's a monumental PITA to run during an automated build.

So what happens in practice? Nobody tests them. (Hooray! Problem solved!)

The team that's already not building for testability (because the shared database removed that pressure years ago) is also putting their logic in the least testable place possible. It's compounding the problem.

Compare that to business logic in C# behind an interface. You mock the data access layer. You write a test in three lines. It runs in milliseconds. It runs on every build. It runs in your CI/CD pipeline. When it fails, it tells you exactly what broke and why.

I've been doing this for a long time. And I can tell you from direct experience: the teams that have business logic in stored procedures are almost always the teams that don't have automated tests. Every. Single. Time. It's not a coincidence. The architecture made testability so hard that nobody bothered, and over time that became the culture.

The Microservices Blocker Nobody Talks About

Here's where this gets strategic.

I work with a lot of organizations that are trying to decompose their monoliths into services and/or containers. It's often the right impulse — they've got a ball of mud and they want to untangle it. But they keep getting stuck, and they can't figure out why.

Here's why: their business logic is in stored procedures.

You can't give a microservice ownership of its data and its logic if half the logic lives in stored procedures on a shared SQL Server. The whole point of service decomposition is that each service owns its behavior and its data. But the stored procedures are anchored to the shared database — they can't travel with a service. They can't be extracted cleanly. They're the weld points holding the monolith together.

I've seen this pattern over and over. The team announces a microservices initiative, starts pulling services apart, and then hits the stored procedure wall. Now they're either rewriting all the procs (which is a rewrite project — see my post about why rewrite projects are terrible) or they're building services that still call back into the shared database through procs, which means they don't actually have microservices. They have a distributed monolith. Strictly worse.

Your stored procedures are one of the reasons you can't decompose your monolith. Nobody wants to hear that, but it's true.

So What Do You Do About It?

If you're sitting on a thousand stored procedures full of business logic — and a lot of my clients are — don't panic. And definitely don't try to rewrite them all at once. That's a rewrite project, and rewrite projects are stone-cold losers.

Instead, think strangler fig. Pick the stored procedure that's causing you the most pain right now. Figure out what's actually business logic versus what's pure data access. Extract the business logic into a C# service class. Replace the stored procedure call with an EF Core query behind a repository interface. Write tests around the new service class. Deprecate the procedure.

Then do it again. And again. One procedure at a time. It's not glamorous. It's an oil tanker three-point turn. But it works, and it gets a little easier with each one because you're building up the infrastructure — the interfaces, the test patterns, the CI pipeline confidence — as you go.

The goal isn't zero stored procedures. It's making sure you're using the right tool for the job. The goal is business logic that lives where it can be tested, refactored, and reasoned about. In 2026, that place is C#, not T-SQL.

Ok. And now's the part where you tell me I'm wrong. Here's my email address: info@benday.com

-Ben