To SP or not to SP in SQL Server? Alway SP

I have had a lot of emails following Adam Machanic’s recent SP article on Simple-Talk. A few of the correspondents expressed frustration that they couldn’t air their views on the article publicly (a limitation that will be removed on the soon-to-be-launched new site) so let’s use this as a landing space for the debate.

First, I’d like to make it clear that while the article itself was all Adam’s work, the newsletter introduction was mine – as a few people have taken exception to the rather absolute “Always SP” in the heading.

While in article headings one is trying to catch people’s attentions, it was never intended to be willfully controversial and the statement was written entirely in the context of Adam’s central contention that if you need to “decide between ad-hoc SQL and SPs”, then you should use SPs. Many people have written in strongly advocating an external data access layer. I don’t deny this might be a viable approach (though I do take a rather database-centric view myself), but this seemed to me a whole new avenue of debate.

Nevertheless, I tend to agree that there are no absolutes in database development and I fully support an open debate on this issue. I’ve invited everyone who mailed me to post their contributions here, and also asked Adam to stop by and respond to any comments

Look forward to hearing from you.