As a developer I actually prefer to use stored procedures. If only SQLServer had a way to organize them in packages just like in Oracle so you wouldn't end up with thousands of stored procedures and no way to tell what they are used for without opening each one of them (doc, which doc?).

On the other hand, I never understood the usefulness of writing stored procedures and then recompile them every time you execute them. That basically defeats the purpose of a stored procedure except for cases where

SELECT * FROM MyTable WHERE Param = 1

and

SELECT * FROM MyTable WHERE Param = 2

would lead to different execution plans because of the statistical distribution of Param = 1 and Param = 2 is too different. But how would you know that beforehand and for how long would this be true (and what about Param = 3?)?