I wear two hats in my company. I am the lead programmer and the DBA. There is a big debate on this issue in forums like ASP.NET. As you might expect the programmers feel that with caching as well as other programming tricks that database access can actually be faster (or just as fast) using dynamic sql instead of stored procs. Although the majority of my job description is that of a programmer, I prefer to use stored procedures for several reasons.

1. The guys who wrote Oracle or SQL Server know more about performance then the average programmer.

2. Although I don't like T-SQL (I use Sql Server) as much as I do C#, I can encapsulate more complicated data access logic in my stored procedures then I can using dynamic sql.

3. I can add an extra level of security to the stored procedure.

4. The last reason is a reason that most people overlook. I have some users that want to access the database via products like Excel. If my selection logic is in a stored procedure they will not only access the tables the way I want them too, but data access code resides in one place. Therefore changes will be localized. If the selections are in dynamic sql the logic will need to be in multiple places.

I hope this helps.