Paul Stovell just wrote an[other] excellent article. Paul says that (basicly) you should be thinking of interfaces to your database as a contract.

The use of Dynamic SQL (i.e anything other than stored procedures) means that your application is, by necessity, very tightly coupled with the database. So, for whatever reason if the database, or your application has to change - things become difficult. As Paul puts it - you’re developing to an implementation.

Standardising on using Stored Procedures means that either end can change, as long as the interface remains the same.

To be honest, I hadn’t thought of Stored Procedures in this light - I have (for some time now) thought of Stored Procedures as just as great way of seperating your data logic, from your UI/Service Code. Being able to change a database structure later was an unintended (but worthwhile) bonus.

I do tend to like putting core business logic in the database too - where it makes sense, at least. Stored Procs arn’t simply just “Saved Queries”, They’re a smart way of ensuring all users of the database get the same correct results, faster. 

On a completely unrelated note, I am now an accomplished SMS Spammer bulk messenger. I’ll try and post more details later. Suffice to say that the next time I see “ReDim” next to an array shared amongst multiple threads - I’m going to scream.