To use Stored Procedures or not.

A while ago, Frans wrote   Stored Procedures are Bad, M'Kay .  None other than Adam Machanic  chimed in, arguing essentially the opposite    .The always insightful Anatoly Lubarsky gives his take on it and well, my head is spinning.  I was Mr. Stored Procedure until Frans schooled me on it.  I still like them and I still use them, but not nearly as much as I used to and basically, Frans cured me of my superstitions with respect to stored procedures (and yes, many people out there use them because of superstition more than through reasoned analysis - although I'm not saying that's the case for everyone).

The only hole I could never reconcile with Frans' argument was with security.  Injection attacks can be addressed through parameterization so the old cannard about injection and dynamic sql isn't a neccsary problem. However even with role based security, it has always seemed to me that you can grant users permissions to procs (through roles ideally) and nothing else. This means that other than execute existing procs, they can't do anything.  AFAIK, this isn't possible with dynamic sql statements. to that end, I've thought that stored procs were ultimately more secure.  HOWEVER, how many people that use procs actually restrict stuff like this correctly?  I've seen a lot more that don't than that do.  Many people act like sticking it in a proc makes it perfectly secure and that sure as hell isn't the case.

The main reason I've liked them is b/c of flexibility.  I can change a lot of things behind the scenes without needed to recompile (more particularly, redeploy).  Granted tables don't change so frequently this is a big deal, but being able to change the user interface and all people to access 'new' functionality, driven totally by stored procs and db entries has always been pretty cool to me.  I buy the encapsulation argument from Adam.    I really like Anatoly 's argument about transactions since I have always been uncomfortable firing transactions client side.

The performance issue isn't one that I've looked into enough to have an opinion on. I've heard advocates from both sides claim that one is faster or that there's no difference.  I've tried testing it a few times and couldn't get conclusive results (and because I was lazy, I just figured I'd take someone else's word for it). Problem is I've heard it too many ways to be sure. This would be a great test to write - and will give me something to do tonight.

Interestingly enough, the Points of view here are all from people that are among the brightest cats out there.  If you've read or followed Adam, Frans or Anatoly, then you no doubt respect them b/c they are all bright as hell.  And none of them run their mouths about stuff that they are unsure of.  And Frans lives in a country with a gay necrophiliac duck (no, I'm not making fun of the Netherlands, I just love talking about the gay penguins in the Berlin zoo or the Dutch duck whenever i can).  Plus, it's a proven fact that you need a Ph.D to pronounce the city where Frans lives so you have to be smart just to live there [It took me about 20 attempts before I could pronounce his town correctly] Seriously though, all three of them are heavy hitters and raise some good points. And the most important thing about this debate is that it's happening. Doing things for superstitious reasons is silly and something we've all (at least I know I have) have been guilty of.  I'd really like to see how this discussion goes b/c they are two different perspectives for sure, although they are touching upon different areas - (unlike Frans first post where he just blew holes in Rob's argument [no disrespect to Rob on that one - it's just that Frans really did a thorough job addressing each point and countering it]).

So what do you think?  Did they miss anything?  Maybe Bill , Sahil, or the ADO.NET Guys will chime in ;-)