If I can influence the decision it's stored procedures for everything and no ad-hoc SQL ever. Not even for selects.

This way I know what queries are run on my database, can do static analysis, generate and search through the execution plans without slowing down the system with tracing and running the risk of missing a vital query that runs only occasionally, make changes to the layout and hide them from the application(s), ensure the data consistency and business rules, ...

I've been pretty anal about this in my main past project and I do believe it paid off. Stored procedures and generated code.