I suppose I can consider myself a Stored Procedure "fan". Treating a database like a object and exposing "methods" for others to use is a nice way to encapsulate its internal structure. However, almost every application comes with the need for searches that can define different criteria based on users' preference/knowledge. These situations would require building a query on the fly and would lend itself more toward ad-hoc dynamic SQL statements passed in from the application.
I have seen it done before, but I never took interest in how it can be done at the Stored Procedure level. Well, Robert Marda has written an article documenting the issues with building a dynamic Stored Procedure. Something to refer to when I have the time/need to read. ;-)