Several years ago there was a big WTF moment for me when I read Frans Bouma's post refuting all of the claims people made for using stored procedures.

Jeff Atwood summarizes Fran's post here.

Every reason I had ever heard for using them over inline SQL was addressed. Ad-hoc (inline) SQL is brittle, it's easier to secure access to data in the database, and performance. The latter being the primary reason why I chose stored procedures.

I don't really have any arguments against their enlightening views. I'll even add to that the idea that if your database logic is in your database, you are tied to that database. It seems the general consensus is that a mixture of stored procedures (for specialized situations) and inline sql (for crud) is the way to go.

So why on earth do I still use stored procedures?

1. I've never experienced pain in maintaining them or debugging them.
2. All of my crud operations and several selects are generated for me. I just compile them. And all of my code to call that logic is also generated for me.
3. I always know what each procedure does because of naming convention.
4. I have been able to resolve production issues on several occasions by tweaking a procedure and not having to re-deploy compiled code.
5. I've never had an application that needed to be database agnostic after development began.
6. This is one of those specialized situations, but it is nice to be able to limit data returned to the application for paging.
7. It's minor, but it seems to me that taking cycles to dynamically generate SQL and then sending that statement over the wire is more overhead than just sending parameters for code that can exist in the database.
8. I like the idea that database code exists in the database. Most dba's I have worked with do too.

I don't do any heavy lifting in the database. I like my database primal. Grog save data, Grog gimme data, etc. Anything that starts to feel like business logic in SQL gets moved to business tier and is replaced by SQL to retrieve only the data that is required for that logic.

I'm not knocking one way or the other. I just haven't seen the light on why I should change. A lot of this becomes moot with a proper caching anyways.