Well I am not going to say what's better or what I prefer, but I can only give my view of things.
What everybody seems to forget is that an external person with very good understanding of SQL (commonly know as a DBA) can solve performance problems when stored procedures are used.
And I totally agree that Stored Procedures are overkill for most of the the simple CRUD functionality. So I am not going to start a debate about that.
But just one question for all those who are against stored procedures.
What if the generated dynamic SQL performs bad. If a SPROC was used a SQL DBA could have tuned the query.
And by tuning I don't mean adding some indexes, but maybe cursor's / memory tables / views / CLR etc. Just to get the results in the fastest possible way.
How would you do this with dynamic SQL ??