Good and Evil in the Garden of Stored Procedures

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.

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at
This entry was posted in Database and Persistence, Ranting. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Johnny

    For me the business logic should be at the database level as much as possible because the databse represents the state of the business and any changes should be made there implementing all the rules.
    It also allows to easilly create user interfaces from any device; Internet, mobile, desktop etc; you can imagine withouit having to ever having to test code because in reality, the code does nothing, it is a simple view layer (except of course for all the access security that shouldn’t be part of the database dah!).
    And to make life easier I use a lot of .NET functions in the database, this permits you to write very complex code, using C++,C#. There is a bit of a learning curve at the beginning to really understand the SQL influence on the code but really worth the time. You can publish this way quite complex business logic without having to write in T-SQL that is true, is quite limited.

    I sincerelly think that today, we shouldn’t limit code to it’s platform. Business logic should be universal in the business, and the code should be a means of starting these processes. This way we can easilly accomodate any any platform for the users and not have the rewrite business code each time a new computing device comes into the business.

  • Jason Jones

    1. If business logic should be in the middle tier, it is also true that “middle tier” is a logical concept that can be implemented just as well through code that happens to be written in the form of stored procedures that happen to be enclosed in the same RDBMS as the data. In other words, if you have business logic in stored procedures, then your middle tier consists, at least in part, of stored procedures. Problem solved.

    2. Where does “business logic” begin and end? Are the kinds of rules enforced by foreign key constraints and unique value constraints and value check cosntraints “business logic”? If not, but then a stored procedure is used to enforce a rule that can’t be encapsulated in a column or table constraint, has that rule suddenly become a business rule that doesn’t belong in the RDBMS at all? Should this important rule be left to every application developer to remember to observe? Or should a freestanding middle tier be built, and should applications have to access data through it, for the sole purpose of enforcing this one rule?

  • Phil

    If everyone in your organization use stored procedures then you better use it too. If everyone write inline SQL, then follow suit. Thats how I sink the endless debate. I know a guy that insisted on inline SQL in a company where other developers wanted stored proc. His code is universally considered bad. There many be cases of the reverse. Just follow suit and call it a day.

  • marky

    Interested if you know of any tools (ideally opensource) which can analyze stored procs/ other sql code and produce any stats on quality or complexity rating etc.

  • Slevdi

    @jeremy “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.”

    This depends. If the business logic is really to maintain the integrity of the database, then it should be *in* the database. If it affects workflow but not data integrity, it should be *out* of the database. That said, much business logic (including workflow) can be – and usually is – controlled by data in the database, so the right place to do this kind of business logic is also in the database. Your other examples were presentation and application logic, and I agree totally with you. All this IMHO of course.

    As far as using the programming language to develop SQL, I have found it fine for simple one line statements. But I have only used one line statements in simple situations. Most database accesses are complex in my experience and I couldn’t imagine coding them in C# (even with an ORM query language) when there is usually a great visual interface available in most RDBMSes.

    I guess I am disagreeing with you.

    I prefer to develop by joining the tables visually, adding sub-queries and unions that were developed in a separate visual interface, and do immediate testing without having to recompile the application.

    Instead of developing query languages in application code like ORMs and LINQ – all of which I regard as retrograde steps in computing – , it would be much more sensible for vendors to improve the productivity inside the db and provide better tools to manage the development of tables, views, stored procedures and user-defined functions. TDD for sprocs would be a good start, as would built in source control.

    Your article, to be fair, points out the evil of *misusing* sprocs. I agree on that one, but surely the answer is to give your staff some db training if they are so bad at it? Not just throw out sprocs. Check your budget for training and you will undoubtedly see 0$ for relational database courses. Just guessing, but I bet I am right.

  • Richard Lack

    Interesting comments, sam. A few things that came to mind reading your comments:
    “[With stored procedures] If you know what you are doing, you don’t have to duplicate code using T-SQL. Yeah, it’s harder to avoid duplicating code, but you can minimize the duplication.” If it’s harder, then that means it takes longer, and there’s a much greater change of making mistakes in the process. That means it takes longer and costs more money, requires smarter people, or produces a worse product, or some combination.

    You mentioned “don’t blame the technology, blame the person who didn’t know what they were doing when they wrote the code.” It’s not the technology so much as the way it’s being used.

  • sam smith

    I’ve seen tons of C#/C++ code that is procedural in nature and has lots of duplicated code. If you know what you are doing, you don’t have to duplicate code using T-SQL. Yeah, it’s harder to avoid duplicating code, but you can minimize the duplication. In short, don’t blame the technology, blame the person who didn’t know what they were doing when they wrote the code. or blame his manager.