Red-Gate just published an interesting editorial written by called "To SP or not to SP in SQL Server".
Douglas adds his voice to the stored procedures versus ad hoc SQL (dynamic SQL, choose your verbiage) debate. The was raised some time ago by Frans Bouma in response to a.
This is probably one of the most valuable dialogs the community can engage in. It surfaces much understanding about application data access architectures and approaches. It appears though that the dialog is broken, and this seems to stem from a couple of assumptions that are damaging to the communicability of the dialog.
When the stored procedure camp talks about stored procedures versus parameterized queries, it typically speaks about a broad swath of approaches that frames the strengths and weaknesses of stored procedures over ad hoc SQL at large. The problem is this broad swath. The “other camp” isn’t broadly speaking about parameterized queries at large, it's talking specifically about mapped data access approaches and technologies wherein parameterized queries are employed. Frans isn’t talking merely about firing any and all SQL command text form the middle tier to the database as an unquestioned silver bullet solution to all data access problems, he's talking about the judicious employment of a set of advanced data access technologies in such a way as to overcome the traditional limitations to agility and the absence of the domain model in typical data pipeline-oriented apps.
The stored procedure camp is trying to explain to the ad hoc SQL camp why stored procedures at large form a better set of approaches than ad hoc SQL. Meanwhile, the ad hoc SQL camp is trying to say, "Er… are you talking to us?? We're not the ad hoc SQL camp… we're the mapped data access camp. The ad hoc SQL camp is around the bend and over the hill, first door on your left." Mapped data access technologies make use dynamic parameterized query command text generation in the middle tier. It does so much in the way that ASP.NET ultimately ends up rendering raw HTML. Mapped data access achieves its outcome through the use of an advanced transformation framework, not by directly coding raw data access instructions.
An example of this disconnect is apparent in Douglas' study of the performance of a data retrieval operation that is best-served by temp tables and stored procs. The comparison against ad hoc SQL produces fairly predictable results… the stored proc exhibits greater performance. Well, duh! Mapped data access doesn’t supplant stored procedures for tasks where stored procedures shine, it supplants them for plain old object persistence. And if you've got no notion of a domain model in your app, or you've got a physical data model that was built in absence of domain-oriented analysis, then maybe O/R mapping shouldn’t be in your tool kit. In absence of a serious mapped data access framework, I'd always use stored procs for object persistence, but there are better tools for that job. You don’t aim an O/R framework at every data access problem under the sun.
And of course, you can't assume that mapped data access supported by a solid framework is anything like what you might believe it is from the perspective of mere ad hoc SQL. This would be like trying to make a value judgement on ASP.NET from having only ever done web work with raw HTML. And like the ASP/HTML analogy, when a high-level data access framework is inappropriate to the task, we defer to a lower-level framework, such as raw ADO.NET and stored procedures.
When the debate is broken down to stored procedures versus advanced mapped data access frameworks, the pertinence of the current climate of the debate simply dissolves. The comparative dialog needs to remain in the realm of the capabilities that the data access frameworks exist to address. Otherwise, there's really no dialog at all - just a bunch of human white noise.
Having built systems based on stored procs and mapped data access, I can say that from my own experience that the apps based on mapped data access are more maintainable by orders of magnitude. Performance degradation is minimal enough to be irrelevant, and data access control is manageable. My experience tells me that mapped data access can be employed successfully in most applications, and it also tells me that there are some applications – especially where a poorly modeled data base is in play – where it isn’t a good fit.
Mapped data access is a paradigm shift. You can't easily compare the practice patterns of call-level interface and stored procedures directly with the practice patterns inherent in mapped data access and expect to derive meaningful conclusions right off the bat. You’ve gotta take the entire set of practice patterns into consideration.
The opposition to parameterized queries is well-founded. Now, if the stored procedure camp could only lay its hands on the raw ad hoc SQL camp, it would make for an interesting smack down. For the time being, the dialog is stunted - it's like listening to someone debate about how HTML is a really poor technology for building Windows forms. It's obvious to everyone but the speaker, who mistakenly believes that his audience is defending a position that it is in fact not defending.
We need to start talking the same language from the same experience, or else we really have nothing to talk about at all - unless we're just talking to ourselves.