I’m throwing my hat into the ring here.

So a bit ago, Bill Ryan posted a link to Frans Bouma responding to a post by Rob Howard regarding Stored Procedures.  Later, Sahil responded to the lot of them.

I will not go out on a limb and say that Stored Procedures are good or bad.  I will say that they are sometimes overutilized, sometimes underutilized, and that whenever you talk to a software developer (including myself) he generally doesn’t fully understand the uses for Stored Procedures, what’s good and bad, and the inner workings of SQL server on the whole.

On to the points:  

Bill and Sahil like sprocs because of the central nature.  You don’t have to recompile and redistribute the app if you make a sproc change.  Well, that’s correct to a degree.  What made you change the proc is the real question?  If you make any structural change to the database, and have to change the sproc to reflect it, then guess what?  You’re going to have to change the consuming code.  No getting around it.  If you add a column, remove a column, change a datatype, almost always you will have to change the code that calls the sproc and does something with the results, at least if the sproc returns a recordset.  If it’s just doing a count or a number or something, maybe not.  Further, depending on the database/code location and relationship, this may be a moot point.  For instance, if you run a web site, with the db and the site on your network, then there is generally going to be no benefit of changing code in the db over the web site.  If it’s a desktop app, and the database is hosted centrally, then yes, it may be more of a pain to change the app than the db.  If it’s an on-site (in your company) app though, maybe you should plan ahead on an upgrade process.  If it’s at a client’s location, you’re going to have to get to their system anyway, so depending on the setup, one change may be less of a hassle than the other.  But I don’t think this issue by itself is a good argument for or against sproc usage.  This is a question of logistics, not code.

Sahil talks about injection attacks.  Total crap.  You don’t need sprocs to solve injection attacks, you need a security conscious developer.  You can stop injection attacks just as well with non-sproc usage as you can with sprocs, it’s just that doing it with sprocs requires less thought.

Let’s talk about DBAs for a minute.  I’ve never actually worked with one…have you?  I’ve worked with guys that think they are DBAs because they can write a proc, and know what 3NF is, and can make a table…but what the hell is a DBA anyway?  What are they supposed to do?  And as for the DBA “owning” the code because he can write better SQL than you…please.  Sahil…tell me you don’t really believe that.  I may have to look up this command and that command when I want to write a more advanced SQL statement, but honestly, I’ve yet to meet a DBA-type that knows how to write SQL that an application developer needs.  Or that understands the kind of table structure that is best for the application, not for the DBA.  DBAs live in their own world, where all that matters is the database.  They don’t see the whole picture.

Security – total crap.  Stored Procedures only give security to the lazy.  If you don’t have security checks and balances before someone gets to the code that talks to the database…you’ve not done your job.  And while we’re on it, if you have SQL in your app, vs SQL in your database…there are way more people in the world who can r00t ur b0x0r with their l337 hax0r ski11z and get your sproc than can decompile your code and get it that way.

Frans successfully debunks the “pre-compile for performance” myth, so I don’t have to go through it here.

So my thoughts: 

Do I think puting inline SQL into every part of your app is good?  No.  Whether you are O/R mapping or not, if your data access is in your code, it should be centralized into its own library.  And even if you are using primarily sprocs, you should still centralize the access to the sprocs in your code.

Do I think sprocs are bad?  No, they can be extremely useful.  Frans says sprocs for CRUD are passee, and I tend to agree.  However, some things a stored procedure just make sense.  Like say, analytical SQL for reporting.  Rather than put the SQL in your report tool, put it in a sproc.  That way you aren’t bound to the reporting tool in any way for the data.  Also, if multiple disparate systems are going to consume the data, systems that you aren’t writing yourself and have no control over, then throw it in a sproc.

I think stored procedures have their places and their usefulness, but I think that a lot of developers are just misguided in their opinions.  They’ve been browbeaten by DBAs and so they’re like the sproc zombies.  Or they never learned to really write good SQL, or really learn what SQL server can do, so they are vehemently anti-sproc (please know that I’m not putting any of the guys above in these descriptions, I know that’s false.  I’m just making generalizations).  Or they are jumping on the bandwagon of O/R mapping and don’t see any reason to ever use a sproc again.  And so on and so on.  Stored procedures, and more importantly the reasons for and against using them, are simply misunderstood in the developer community.  Take the pre-compilation myth…I was always told that.  I know a lot of developers who always believed that.  But go to books online and there it is…it’s not true.  I think we are forgetting one of the most important things of developing systems.  Know the technology you are putting into your system, know the strengths and weaknesses, and know how to use it when you are designing it into an application.  Do you do any less due-diligence when integrating a new library, or utilizing a new part of the Windows API, or evaluating commercial components?  You probably know the pros and cons and uses of those things inside out before you write one line of code to integrate it into your system.  Take the time to do the same with SQL and stored procedures.