The Case for Business Logic in Stored Procedures
This is the second post in the “Where Does Business Logic Live?” series. In Part 1 I laid out the terms of the debate and split “business logic” into five different kinds of logic that behave very differently when you decide where to put them. Now it is time to make the case for one side properly – not as a strawman to knock down, but as the strongest version of the argument a working DBA can make.
I am going to argue for putting significant logic in stored procedures. In Part 3 I will turn around and argue, just as honestly, for keeping it in the application. If you only read one of the two, you will get a distorted picture, so consider this half of a matched pair.
The case for the database is not nostalgia. It rests on a handful of things SQL Server is genuinely, structurally better at than any application tier – and on a set of failure modes that the application-tier approach runs into over and over again. Let’s go through them.
The Database Is the One Component Every Writer Passes Through
Here is the single most important fact in this entire debate, and it is the foundation everything else in this post is built on: the database is the only tier that sees every write.
Your web application enforces its rules. Good. But the data in your tables is also touched by the mobile app’s backend, the nightly batch jobs, the integration that the partner team stood up last quarter, the bulk import someone runs from SSIS, the ad-hoc fix a DBA applies during an incident, and – five years from now – the replacement application that the company builds when it decides the current one is legacy. Every one of those writers has its own idea of what “the rules” are, or no idea at all.
When you put a rule in a stored procedure and revoke direct table access, that rule is enforced no matter which application is calling. It becomes a property of the data, not a property of one codebase. This is the centralization argument, and it is the most durable one the database side has. The question “how much business logic should the database implement?” has been argued for decades precisely because this point is hard to refute.[1]
Centralization is not free – we will count its costs in Part 4, because a stored-procedure layer that everything depends on is also a single point of change that everything depends on. But the benefit is real: one place, enforced for everyone.
Set-Based Processing: The Thing the Database Does Best
The relational engine is a set-processing machine. Its entire query optimizer, its access methods, its memory grants, and its parallelism exist to operate on sets of rows at once. When you express a business operation as a set operation, you are using the engine the way it was designed to be used.
The trouble starts when business logic lives above an ORM that models the world as objects. An object is a row. A collection of objects is a loop. And a loop over rows is exactly what the database is worst at.
Consider a routine business rule: apply a 10 percent late fee to every overdue invoice in a billing run. In the database, that is one statement:
|
1 2 3 4 5 6 7 8 9 |
UPDATE [bil].[invoice] SET [late_fee] = [amount_due] * 0.10 , [status] = N'Overdue' , [modified_on] = SYSUTCDATETIME() WHERE [due_date] < CONVERT(date, SYSUTCDATETIME()) AND [status] = N'Open'; |
One round trip. One transaction. The optimizer reads the qualifying rows, updates them in a set, and is done. On a million invoices it is still one statement.
The same rule, expressed through a naive ORM domain model, often becomes: load every open invoice into memory as objects, iterate, set a property on each, and save. That is a SELECT to pull the rows up the wire, then potentially one UPDATE per row on the way back down – a thousand, ten thousand, a million individual statements, each its own round trip, each its own tiny transaction unless you are careful. The logic reads beautifully in C#. It performs like a handcart next to a freight train.
This is not a knock on ORMs as a category – it is a knock on doing set work row by row, which is what the object abstraction quietly encourages. An experienced developer using Entity Framework knows to drop to a set-based ExecuteUpdate or call a stored procedure for exactly this kind of operation. The point is that the database makes set-based the easy path, and the object model makes row-by-row the easy path, and for data-heavy business rules the database’s easy path is the right one.
Fewer Round Trips, Less Data on the Wire
Every call from the application to the database crosses a network boundary, and network latency is the tax you pay on each crossing. Inside a data center it might be a fraction of a millisecond; across an availability zone or a cloud region it is meaningfully more. Multiply by the number of round trips and it adds up fast.
A stored procedure lets you do a multi-step operation in a single call. Validate the customer, check the credit limit, insert the order, decrement the inventory, write the audit row, and return the new order id – one round trip, all of it happening next to the data instead of shuttling intermediate results back and forth.[2]
The data-transfer side matters just as much. A stored procedure can return exactly the columns the caller needs. ORM queries have a well-documented tendency to do the equivalent of SELECT * – pulling every column of every table involved, because that is what mapping a whole object implies. Erik Darling, who spends his working life performance-tuning SQL Server, points out that this is not mainly about metadata overhead; the real damage shows up in oversized memory grants, because the optimizer sizes the grant based on the declared width of every string column you dragged into the query, whether you needed it or not.[3] Wide, lazy result sets cost memory, cost network, and push other queries out of the buffer pool.
Transactions That Actually Mean Something
Transaction consistency is where the database’s home-field advantage is sharpest.
When a multi-step business operation has to be all-or-nothing, the cleanest place to guarantee that is inside a single database transaction, in a single round trip, where the engine controls the locks and the durability. A stored procedure can wrap the whole operation in one transaction with proper error handling and a deterministic rollback path:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE OR ALTER PROCEDURE [ord].[place_order] ( @customer_id int , @product_id int , @quantity int , @order_id int OUTPUT ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; IF NOT EXISTS ( SELECT 1 FROM [inv].[stock] AS [s] WHERE [s].[product_id] = @product_id AND [s].[quantity_on_hand] >= @quantity ) BEGIN THROW 50001, N'Insufficient stock for the requested quantity.', 1; END; INSERT INTO [ord].[order] ([customer_id], [product_id], [quantity], [created_on]) VALUES (@customer_id, @product_id, @quantity, SYSUTCDATETIME()); SET @order_id = SCOPE_IDENTITY(); UPDATE [inv].[stock] SET [quantity_on_hand] = [quantity_on_hand] - @quantity WHERE [product_id] = @product_id; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; THROW; END CATCH; END; |
The stock check, the insert, and the decrement either all happen or none of them do, and the locks are held for the shortest possible window because the whole thing executes in one place.
Contrast that with orchestrating the same transaction from the application across several round trips. Now the transaction is held open across network hops. The locks live longer. If the app server pauses for garbage collection, or the connection drops between step two and step three, you are relying on the client’s transaction handling and connection pool to do the right thing. It can be made correct, but it is harder to make correct, and the failure modes are nastier. The database keeps the transaction short, local, and under the engine’s control.
This is exactly the territory the StackExchange threads on “business logic in stored proc versus middle layer” keep circling back to: the closer a transactional, multi-statement operation lives to the data, the easier it is to reason about its consistency.[4]
Security as a Side Effect of the Boundary
One of the most under-appreciated benefits of the stored-procedure approach is that it hands you a least-privilege security model almost for free.
If your application logs in as a principal that has EXECUTE on a set of procedures and no direct permissions on the underlying tables, then the only operations the application can perform are the ones you wrote procedures for. The application literally cannot run an arbitrary DELETE against the customer table, because it has no DELETE permission on that table. It can only call ord.place_order, ord.cancel_order, and the other procedures you have sanctioned. SQL Server’s ownership chaining makes this work: when a procedure and the tables it touches share an owner, the caller needs permission on the procedure, not on the tables.[5]
|
1 2 3 4 5 6 |
/* The application principal can call the procedure... */ GRANT EXECUTE ON [ord].[place_order] TO [app_user]; /* ...but has no direct access to the tables it touches. */ REVOKE INSERT, UPDATE, DELETE, SELECT ON [ord].[order] FROM [app_user]; REVOKE UPDATE ON [inv].[stock] FROM [app_user]; |
That is a dramatically smaller attack surface than an application that connects with broad table rights and assembles its own SQL. It also narrows the blast radius of a SQL injection vulnerability: even if an attacker manages to inject through a parameter, the compromised principal can still only execute the procedures it was granted, not arbitrary statements against arbitrary tables. We will go deeper on the security comparison – including where the application tier has its own genuine advantages – in Part 5.
Keeping Sensitive Data Close
Related to the security boundary is a simpler operational point: the less sensitive data you move out of the database, the fewer places it can leak from.
When a business rule that touches regulated data – personal information, payment details, health records – executes inside a stored procedure, the sensitive values can be read, evaluated, and acted upon without ever crossing the wire to the application tier. The procedure can return a decision (“approved,” “declined,” a masked summary) instead of the raw data. Every tier you keep that data out of is a tier you do not have to audit, encrypt in transit to, and worry about caching it in a log file. For some compliance regimes, minimizing where regulated data travels is not just convenient, it is part of the requirement.
The ORM Tax: What Generated SQL Does to Your Plan Cache
The argument for the database is not only about what stored procedures do well. It is also about the specific, repeatable problems that show up when all the data access is generated by an ORM and nobody is looking at the SQL it produces. This is the part of the case that is most grounded in evidence, because these pathologies show up in real plan caches every day.
A recurring one is the unparameterized, variable-length IN clause. When an ORM translates “give me the rows for this list of ids” into a literal IN (1, 2, 3, ... ) with a different number of values every time – and without parameterizing them – SQL Server sees a brand-new query text on every call. That means a new plan compiled and cached every time, which is two problems at once: constant compilation burning CPU, and plan cache bloat as thousands of single-use plans pile up and evict the plans you actually want to keep.[6] The database-side fix – passing the list as a table-valued parameter to a stored procedure – turns thousands of distinct query texts into one reusable plan.
Then there is the SELECT * tendency already mentioned, the overly wide result sets, and the deeply nested, machine-generated queries with a dozen LEFT JOINs and stacked derived tables that the optimizer interprets poorly. None of this means an ORM is incapable of generating good SQL. It means that the abstraction makes the expensive patterns invisible to the developer, and “invisible” is how they end up in production. As Erik Darling puts it across his ORM writing, the recurring theme is not “ORMs are bad” – it is that abstraction is not magic, and a team that leans heavily on an ORM still has to understand execution plans, indexing, and parameterization, or it will eventually hit a wall.[7]
A stored procedure does not magically write better SQL than you would. What it does is put the SQL in front of a human, in source control, where it can be reviewed, tuned, and given a stable execution plan – instead of being assembled at runtime by a layer that was optimizing for developer convenience, not query performance.
Where This Case Is Weakest
I promised honesty, so here is where the stored-procedure argument starts to strain, and where Part 3 will pick up.
A stored-procedure layer is hard to unit test the way application developers expect to test code. It is harder to refactor with confidence, harder to diff in a pull request if you are not disciplined about putting it in source control, and it concentrates change in a place that multiple applications depend on. T-SQL is not a language most teams want to express complex, branching, multi-system workflow in – and they are right not to want that. A 3,000-line procedure that calls web services, loops over queues, and encodes an entire business process is an anti-pattern, not a triumph, and I have had to support a few of them.
The database is the right home for data integrity, set-based data manipulation, transactional consistency, and the security boundary. It is the wrong home for cross-system orchestration, externally-dependent workflow, and presentation. The strongest version of the database argument is not “put everything in stored procedures.” It is “put the data-centric, set-based, transactional, security-sensitive logic where the data is, and stop dragging that specific category of work up into the application tier where it runs slower and enforces less.”
That is the boundary. In Part 3 we cross to the other side of it and make the application’s case just as seriously – including why “we can put it in CI/CD” is not the trump card it is often played as, because database code belongs in CI/CD too.
If you have a stored procedure that saved a project, or one that became the monster under the bed, I would like to hear about it. You can find me on Bluesky and LinkedIn.
References and Footnotes
- “How much business logic should the database implement?” – a long-running, heavily-viewed discussion on the centralization question. softwareengineering.stackexchange.com. ↩ back
- “Stored Procedures (Database Engine),” Microsoft Learn – lists reduced network traffic and reuse of cached execution plans among the documented benefits of stored procedures. learn.microsoft.com. ↩ back
- Erik Darling, “Software Vendor Mistakes With SQL Server: Selecting Everything From Everywhere” – on how ORM-generated wide result sets inflate memory grants. erikdarling.com. ↩ back
- “Business logic in stored proc vs middle layer (c#)” – a focused practitioner discussion of the transactional and consistency tradeoffs. dba.stackexchange.com. ↩ back
- “Stored Procedures (Database Engine),” Microsoft Learn – documents how procedures act as a security mechanism: users can be granted permission to execute a procedure without holding direct permissions on the underlying objects. learn.microsoft.com. ↩ back
- Erik Darling, “Software Vendor Mistakes With SQL Server: Passing Long IN Clauses To Queries From An ORM” – on plan cache bloat, constant compilation, and why table-valued parameters are usually preferable. erikdarling.com. ↩ back
- Erik Darling, “Signs It’s Time To Switch From ORMs To Stored Procedures In SQL Server Development,” and “On Entity Framework And SQL Server.” erikdarling.com (signs), erikdarling.com (on EF). ↩ back
Next up: The Case for Business Logic in Application Layers – separation of concerns, testability, real CI/CD (for database code too), and integration, with examples across ASP.NET Core, Spring, Node.js, and SPA frameworks.
Part of the “Where Does Business Logic Live?” series – Part 1: A DBA’s Field Guide.