That was the topic of a discussion I had with some of my social network just after new year. Apparently, the discussion was prompted by this posting. There's a lot of good points in the posting, but personally I'm always going to use stored procedures. Why? Well 'cos its a great pattern for two reasons.
Firstly for security. If you create a database api then that gives you another layer boundary at which you can post a gatekeeper to keep out the bad guys. Secondly for testing. If you create you database api and test it then that's it, job done. If each client accessing your database has to write their own (predominatly similar) data access dynamic sql, then each client has to be tested seperately. That's an overhead that I just don't need.
So, for those two reasons above others, I'll be keeping my stored procedures thank you very much.