To use or not to use: Stored Procedures

There is a hot debate between Frans Bouma and Rob Howard on whether using SP in data driven application is a good/bad solution. Here are summaries of the crossfire:

What stung Frans most is the productivity. He admitted 2 months of development had been lost because tried to create a DAL with SP's and a flexible usage of that DAL in C#/VB.NET code. "I failed miserably, because the SPs API is too inflexible."

1. Flexibility, Productivity.
Rob argued using Dynamic SQL (DSQL) introducing high coupling between code and backend while SP provides an abstraction between Business Layer (BL) and DB, hence reduce the coupling.
Frans pointed out that a relational model has always coupling with the application. On the contrary to what Rob said, change to DB schema involves more work: change SPs signature, API that expose SP, caller components that target the API.
My experience coheres to Peter's comment The main bottleneck ... is programmer productivity...Stored procedures are hard to debug, hard to deploy, inflexible.
Code is very difficult to maintain. I have done an intranet ASP.NET application. One-month work, this includes about 110 SPs (24 tables) and corresponding DAL to expose them, all need to be comprehensively tested. And each time when change request comes in, it is an arduous task than a challenging one to dig in the dependencies. This pattern is similar to what Paul Wilson described, but I am the only one on the whole project. I reckon by using DSQL, I can cut down 30-50% of the development time yet boost the morale. (DAL and CRUD SPs are not fun in any sense.)

2. Performance
This could be the major selling point of SP over DSQL on two commobeliefses: 1) SP is pre-complied and execution plan is cached. 2) Cut the client call round trip to DB.
Guess what they are not true or partially not true.
-- In SQL Server (V7, 2000), SP is compiled at execution time and execution plan is cached. So as the DSQL. (SqlServer's Books Online: SQL Stored Procedures)--We are talking parameterized queries not ad hoc SQL statements.
-- In Oracel SP are precompiled. So point taken.
-- Round trip could be an issue. However, we also need to consider BL, DAL server boxes are standing next to DB server behind the proxy. Particularly, we can have a whole batch of T-SQL commands in a single call so to cut the round trip.
My take on is: performance issue need to decide by application profiling. Where if it is impossible to have two solutions side by side, we can stress test application (demo-ware). The result should be able to help us justify other pros and cons.

3. Security
It is now clear that both parameterized queries and SP are immune from injection attack.
Frans suggested using fine granulate role base security as a mean to control data access which is common to both SPs and DSQL. However, Phil Scott felt that for even the smallest set of role base security defined on columns level will be a nightmare. A task-base (rule-base?) is more easy to define and to maintain.
Frans also suggested using view to control security. I think this is a good point. DSQL can still taking benefits of indexed view, so does SP.