To use stored procedures or not to use stored procedures

I thought this battle was settled down, and we had all agreed that stored procedures (from now on called SPs) are (for the most part) the way to go when accessing your SQL data

turns out Ayende is not quite convinced yet, in fact it seems he prefers to use no stored procedures at all, I told him SPs can give you scalability, flexibility, security, etctirity, and here's his response

"I completely disagree with this, if you didn't catch on so far. Here is my refutal:

Scalability:
There is no performance advantages to using SP over Parameterized SQL.
Scaling the database is much harder than scaling an application.
Note: I implemented a caching web service once that simply cache data for certain period of time, saving quite a bit from the poor database. Deploy a bunch of those, and you're seriously reduced the amount of work the database has to do.
Flexibility:
Stored Procedures are... Well, procedures. This means that you've all the advantages and disadvantages of procedural programming. On general, I wouldn't call it very flexible. Changing the procedure's internal can be done without breaking clients is possible within a certain set of changes, but major stuff is hard.
A search procedure should be flexible, you should be able to search by any combination of parameters, how easy it is to write this procedure?
Security:
Not applicable in situations where you don't give users direct access to the database, in my opinion. There are things to be said about defense in depth, but I don't think that they should be curried to the point where you are forced to give up significant advantages while keeping the security of the system (using views / triggers instead of procedures, for instance, can result in a situation just as safe, and far easier for the programmers to work with effectively)."

let's see

"Scalability:
There is no performance advantages to using SP over Parameterized SQL. "

...you probably posted that in the incorrect section, Scalability and performance are different things

"Scaling the database is much harder than scaling an application. "
that's just another reason to separate the data rules (SPs) from your application

"Note: I implemented a caching web service once that simply cache data for certain period of time, saving quite a bit from the poor database. Deploy a bunch of those, and you're seriously reduced the amount of work the database has to do. "
you can still cache the result of the stored procedures, I don't see how that's an advantage of not-using-stored-procedures

"Flexibility:
Stored Procedures are... Well, procedures. This means that you've all the advantages and disadvantages of procedural programming. On general, I wouldn't call it very flexible. Changing the procedure's internal can be done without breaking clients is possible within a certain set of changes, but major stuff is hard. "
even if they are just a few, I still didn't have to recompile (and potentially distribute the application) just to make those little changes

"A search procedure should be flexible, you should be able to search by any combination of parameters, how easy it is to write this procedure? "
just as easy as it is to write a query on the app, besides that, I can make important changes to the stored procedures, perhaps look in other tables (history, cache, etc), take other parameters to use the same SP in another application, etc; again, without having to recompile my app

"Security:
Not applicable in situations where you don't give users direct access to the database, in my opinion. There are things to be said about defense in depth, but I don't think that they should be curried to the point where you are forced to give up significant advantages while keeping the security of the system (using views / triggers instead of procedures, for instance, can result in a situation just as safe, and far easier for the programmers to work with effectively). "
secure programming is all about good practices, the stupid SQL injection is because people just do

"select * from users where userName = "+UserID

if they would've used a stored procedure, that would've been ok, the same thing would've been accomplished by using a parameterized query as you point out

but is much more than that, security is not only who can get access to your data, is who can mess up with your data, and changes to that are far easier accomplished using SPs; is the exact same concept you use when you break up your code in files and classes, you give each class a responsability (I hope), is the same thing with the database, you give the database it's responsability and let your application do it's responsability. it's about using the right tool to get the job done