Why I Hate Stored Procedures: A Manifesto

I am not a fan of stored procedures. I really dislike them, in fact. I know they have a place, but, in general, they’re insanely over-used.

Specifically, I do not believe in using stored procs to wrap simple SQL. If you have a simple INSERT or DELETE, you don’t need a stored proc for this. Seriously – regular old SQL still works pretty well.

The main problem with stored procs is that you’re creating yet another layer of abstraction for your app. And, in most cases, you’ll find that your stored proc layer starts looking a lot like your API layer. Usually identical, in fact. You have a DeleteArticle method to which you pass an ArticleId…which turns around and calls a sp_DeleteArticle stored proc, to which you pass the same ID.

If you find that you have a problem in your app, you now have to hunt through yet another layer of stuff. If articles aren’t deleting, is the problem in the front end UI? In the business layer? In the object persistence API? In the data access layer? In the stored proc layer?

Worse, SQL is a really obtuse language to do finely-grained logic with. I promise you its unlike the language you’re doing your actual programming with – C#, Java, whatever – so you’ll need to maintain yet another section of your brain for it. Oh, but you have a DBA sitting around at your beck and call to write your SQL? Well, lucky you, first of all…or maybe not. Now you are dependent on some other guy to get you access to your data. Need a change to how the data comes back? Open a ticket with the DBA group, buddy.

Additionally, now you’ve gone around your carefully-crafted business API. A lot of the data logic in apps is at the API level, and SQL runs under that. Are you going to duplicate all your logic at the SQL level, or risk anomalies because SQL did something that your API doesn’t allow?

This would be different if you got a lot of benefit from the proc, but most of the time I see procs doing nothing but wrapping single SQL statements. This usually doubles the calling code in your API, plus introduces another entire logical layer at the database level.

So, what are the benefits? A few:

• If you’re doing some really complex data retrieval which involves temp tables, cursors, or other logical complexity at the pure data retrieval level, then stored procs are great. You don’t want to bring a bunch of data back to the client and use it to just go back and call other procs.

• Stored procs are good for gluing different data stores together and generating data that doesn’t really exist in the returned form anywhere. You can pull in data from all over the place, do some backflips with it, and send it back as a simple recordset.

• If you have security issues, stored procs can be good as well. You can give someone EXECUTE permissions on a proc if their needs are limited and their access should be too. (Though, you can usually do the same thing with a well-written view.)

But what about performance!? Stored procs are faster, right?

First, not usually. SQL Server in particular really eliminated the performance difference years ago, so it’s more or less a wash.

Second, you’d have a tough time finding me an app where the performance difference between stored procs and raw SQL was the last remaining optimization. I promise you that in the average app, there’s about a hundred things that you should optimize before you start worrying about the performance gain from running everything in stored procs.

Jumping into stored procs to optimize your app is like saying, “I want to win the Mr. Olympia bodybuilding title, so I better run out and get a tan.” I’m willing to bet there are another couple steps in there you might want to consider first.

But what about security!? Haven’t you ever heard of SQL injection attacks?! Relax, Sparky. Too many people think that not using stored procs means creating a gaggle of SQL with StringBuilders and risking SQL injection and all that.

But this is not so. A good, solid database abstraction layer (ADO.Net, ADODB for PHP, etc.) will protect you from SQL injection and allow you to do parameterized queries just fine. It’s very, very rare that I manipulate SQL at the string level anymore.

But stored procs let me centralize data access!! Centralize your data logic in your object API. You’re already doing that now, right? Right!?

I’m not alone in this antipathy – there’s a great blog post on this by Frans Bouma in which he plainly states his hatred and then defends it through hundreds of comments. It’s totally worth reading.

I’m envisioning a new 12-Step group for recovering stored proc addicts. Will you join me?