My latest addition to the blogging world was received with mixed feelings. The general idea of that post and the code that came along with it was to make it easier to work with "ad-hoc" queries in your code. The discussion in the comments was not so much about my proposed solution for handling ad-hoc queries as it was about sprocs vs ad-hoc. This is a really old debate, kind of the "el classico" of data access but I would like to elaborate on some of the comments and explain what motivates me to avoid sprocs as long as possible.

Read more here https://lowendahl.net/showShout.aspx?id=140

In some of the comments, sprocs are looked at as a single point of integration. The scenario is that multiple applications should access the same data and do so through the same sprocs. The argument is that this will enable re-use and add the ability to make changes that affect all applications that uses the sproc.

"Bob" comments that:

"an added benefit of SPs is that they provide a centralized, unified view to the data. When business rules change, they only have to change in one place -- the SP"

I can understand that in legacy systems, heck I'm even a father to a couple of those, sprocs was the single point of integration and really the only way to share rules and data access cross-platform and cross-applications. But it was a long time ago that we really could count those solutions as any kind of best practice. In 2007 reuse of rules and processes is best enabled by a SOA.

There is also other things with having rules and process expressed inside sprocs that bothers me. First of all SQL is really not a language that excels in describing and enforcing business rules. It was created to be effective for CRUD, SET based operations, and not to capture business intent. Languages like C#, Java and Ruby are much better at that.

Secondly testability goes out the window the moment I start to write my complex price calculations in SQL, there is just not enough support to do isolated unit testing on single sprocs without directly hitting "integration testing". Whereas application languages and frameworks have extensive support.

Tests are a key component in successfully changing any business rules that are already in production. Without them, you will just not know what you broke until it is to late, with hours of debugging, fixing and work around as a result.

These two areas are enough to make me keep any kind of business knowledge in SQL but there is a lot of others that count to, such as:

* The implicit constraint on scalability that Evan Hoff wrote a nice summary about over here: http://www.evanhoff.com/archive/2007/06/05/19.aspx .

* The inability to create flexible queries with where clauses, order by, group by etc that are based on decisions made by the application.

* The static way which they are pre-compiled, which lures people into "performance" traps only solved with the "with recompile" (for sql server) flag on a sproc call, effectively re-rendering the query plan anyway.

* The fact that SQL is just plain ugly and hard to maintain when you get to a certain number of LOC.

* As Mats pointed out in the comments here of the previous post: Try to version sprocs and the dependency between them, you will certainly die of old age before you figure it out.

These are the reasons why I stay away from sprocs.

There are reasons when I might want them, like for granular security (as OJ mentions).

Or.. ehm.. Problem is, I don't (like Phil also commented in the post) like to handle my users / groups in the database. I want every application / service to have it's account and throttle all queries through there. That gives me the same security context, which effectively allows me to use the built in Connection Pooling. Additionally I get the benefit of administrating access for applications and don't have to worry about 100's of roles or 1000's of users.

So no, there is probably nothing that would make me use sprocs (well a gun to my head or a million dollars aside)