There's plenty of queries you may find yourself scattering throughout your application that you don't want to have in the database as a stored procedure, if for no other reason than it being overkill and extra work.

If it's business logic in the query that might get re-used across independent applications (e.g., cancelling an order), then I would think a stored procedure is better. But if it's specific to that application (e.g., fetching title+description+publication date of the five most recent blog entries for a side panel), I wouldn't care to put that in the db as a stored procedure.