A number of years ago, I went to work for SportSoft Golf (later Golf Society of the U.S.), one of the dot-com companies of old. Like many dot-com companies, SportSoft Golf failed not long after September 11th. While working there, the change from doing both fat client and intranet development to doing only Internet development was jarring, as was the change to using only stored procedures (SPs) and moving away from ad-hoc SQL.
Jim, my boss at the time, was a longtime SQL Server developer, and was most comfortable working inside SQL Server. I initially resisted but was eventually won over, and to this day virtually all of my database access code uses SPs.
The topic of using or not using SPs does not reach the level of religious fervor in many quarters. I became aware of the debate back in November 2003 when Rob Howard, then a Microsoft Program Manager and now the founder of Telligent systems, posted a lengthy blog entry on why all SQL Server developers should be using SPs. Frans Bouma, a Microsoft MVP and developer of LLBLGen Pro, an O/R mapper, posted his rebuttal. There have been a number of discussions since then, often shedding more heat than light.
Not to pick on Rob and Frans but rather to illustrate the biases of a number of the folks involved in the SP versus ad-hoc SQL debate, I will use these gentlemen as examples. I know Rob personally and like him. I have met Frans only in the virtual world, but must say I respect his abilities, and even when I disagree with him, I find him agreeable. That said, both advocates have some bias that I imagine influences the way they feel about SPs. Rob's is pretty obvious. He was a Microsoft employee when he wrote the blog entry mentioned above, and even now he does a fair amount of business with Microsoft. Microsoft has an interest in folks taking full advantage of all that SQL Server has to offer (including SPs), as a way to lock those people into using Microsoft SQL Server. Frans develops an O/R mapper, and doing an O/R mapper that needs to support multiple databases is clearly easier using ad-hoc SQL.
They are both pretty smart people, smarter than most.