Frans Bouma has brought together a set of posts about stored proc v ad hoc SQL usage. Yay! A new Stored Proc vs. Dyn. Sql battle!
On reading the posts it is clear that each has its place, if only everyone would agree.
I have to comment on the fact that many people who make views about bad this and poor written that are generally experiencing them in an enterprise environment. More and more systems nowdays aren't such systems, don't process 100's of transactions per second so why do they need to eek out that nth degree of performance. Most servers these days are hugely powerful compared to previous version, whereas in the client world the applciations have absorbed this increase, the database hasn't so as servers get faster so do the database servers.
For these reasons it is a fair argument to say "we are develop code in a way that means we can develop systems faster", if this means using adhoc sql then why not, i know form biter experience that managing 10 sps that do the same thing but with a slightly different where clause isn't great for supportability.
Which ever option you choose make sure you have a DBA that can review any adhoc sql your system produces to make sure you haven't got some nice cross joins occuring.