Periodically I see or engage in an argument over the usage of stored procedures. I think a lot of these types of arguments are somewhat unproductive because the combatants are thinking and talking about radically different things. Besides, I think the argument over the proper role of stored procedures in data access is often obfuscated by the misuse of stored procedures for things other than data access. My opinion has changed quite a bit through the years, so I try to stay out of these arguments.

However, my development group has to make a change soon in our philosophy for using stored procedures (or hit a brick wall). The way my company has used T-SQL in the past is jeopardizing our future development (besides, I’m an Oracle guy and I think T-SQL is butt-ugly). We’re a product development company that has grown by leaps and bounds from a startup venture and the existing codebase has grown somewhat chaotically. To keep the growth going and accommodate new customers both here and abroad we’re going to need to localize the application for different languages and port our main application to Oracle. Toss in the ongoing adoption of TDD and continuous integration as a means of creating better software quicker, and the usage of stored procedures has to be reevaluated.

What’s Good about Stored Procedures

Before I start ranting about the grotesque misuse of stored procedures I’m dealing with, here is what I think is useful about stored procedures.

Using stored procedures externalizes the SQL from the compiled code. I really like having the SQL in an easy to read format instead of built by string concatenation in the code. This is also great if you need to optimize the stored procedure for performance. I haven’t seen that come up very often, but it only seems to happen on systems where the SQL is embedded in the code. There’s also the issue of allowing a DBA to alter or write stored procedures independently of the code. I have generally had control of both database and code throughout my career, so I’m always suspicious of DBA’s writing stored procedures instead of developers. I do think that dynamically constructed SQL can result in systems that are hard to debug, but that can be beaten with just a little judicious instrumentation in your data access layer.

Performance is an advantage of stored procedures, but maybe not enough by itself to justify stored procedures over other approaches. I think the performance advantage is either largely mitigated by the ability of newer database engines to cache execution plans or simply a case of premature optimization. If you have an opportunity to batch up a set of updates or simply reduce the amount of information you send over the wire to the database a stored procedure certainly leads to a performance improvement. Otherwise, I think the stored procedure advantage is too slight to be a serious consideration.

Security is another advantage that is often cited as an advantage of using stored procedures. On one hand, stored procedures quickly mitigate vulnerability to SQL injection attacks. On the other hand, the database can be tightened down so that no ad hoc SQL can be executed, stopping security breaches right at the start. Personally, I abhor this idea myself because it drags down the development team’s velocity. I don’t see why it’s necessary, but it is an option.

Most of this goodness can be happily achieved with nary a stored procedure. Parameterized queries can eliminate SQL injection vulnerabilities while providing comparable performance. The data access layer we built (I might release this as part of StructureMap someday) for our last project already externalizes SQL from the code for ease of maintenance and decoupling from the database.

When Stored Procedures Go Bad

While there is nothing inherently evil about stored procedures, developers often misuse stored procedures. I think stored procedures should be nothing but basic CRUD. I wrote in an earlier post that “IF/THEN/ELSE” logic in stored procedures is a design smell.

Stored procedures are a terrible place to create business logic. Development environments for the .Net languages or Java are far better suited for developing applications. Intellisense support, debugging, and refactoring are all inferior with stored procedure languages. T-SQL is a procedural language that pretty well forces you into the transaction script style of organizing business logic. One thing I’m observing in the reams of T-SQL I’m wrestling with is the obscene amount of code duplication. Now that we have to support Oracle as well, this duplication stands out like a sore thumb. An OO language can do a much better job of code reuse.

Here’s my list of things I’ve seen put into stored procedures that should never be in a stored procedure.

1. User input validation that creates the validation messages seen on user screens. I can’t even begin to describe how stupid this is.
2. Creating HTML, enough said.
3. Security authorization logic. This is definitely a middle tier responsibility. I’ve used joins to some sort of permission table in select statements to filter records. I think that’s fine, but using procedural logic or even role checks inside stored procedures is nuts. Security is volatile and sensitive. Put it where it’s easy to test. For that matter, always leave a way to test your business logic without security checks if you can. Security always makes code harder to unit test. By wrapping security up with business logic in a bag of stored procedure goo, you’ve essentially shot yourself in the foot for testability.
4. Business logic. It’s easier to write and test in C#, period. Splitting logic between stored procedures and middle tier code is even worse, but I’ve seen it done many times.