Nice troll bait... I'm a little hungry...
Anyone advocating stance within the development and technicals sphere that says ALWAYS or NEVER is usually wrong. So, saying "ALWAYS use stored procedures" is a good way to demonstrate ignorance. The flip side is this little screed saying "NEVER use stored procedures." Guys and Gals, you do what works to get through the day the best way you can. If that means using stored procedures because of actual benefits that they do offer (more on that in a bit) then you should use them, not follow some dogmatic dictate that shrieks their evil because they don't fit some preconceived paradigm. On the other hand, a very large percentage of TSQL code, for example, the basic three CUD statements needed for most tables, doesn't really have much in the way of benefits by being in stored procedures (assuming it's only doing the work of create, update, delete and other set based operations are not occurring). Do the right thing for the right reasons, not because some artificial paradigm imposed from without says to do it.
Stored procedures are an industry best practice. Note the key word and trick phrase, best practice. They're not a law or an absolute or a THOU SHALT...
All that said, you've got some serious flaws in your argument:
If you read this you will understand that only the part of the query that does not change is precompiled and if the parameters change performance may suffer.
And it may not. In fact, it may, and more often than not, does, benefit from having a reusable execution plan, one that doesn't vary based on the length or quantity of the parameters passed.
SQL Server stores execution plans for any query sent to it, therefore there is no advantage in this department.
Utterly wrong. Read this little entry[/url] on my blog about nHibernate and how it passes parameters. You don't have to change much for the execution plan to be regenerated because of a miss while searching the cache. Just the simple fact of defining your parameters to match your data blows this theory out of the water. Yes, you could make sure that you're passing your parameters appropriately, but now you're starting to write well structured TSQL code using parameters. The step between this and stored procedures is simply one of storage, not structure or purpose.
If any performance difference is negligible.
Demonstrably false. Recompiles and new compiles in a highly volatile OTLP system are a serious bottleneck. If the stored procedures we're talking about here are stupid simple, one table SELECT statements, you have an argument. Everyone that has a relational database where many of the queries are against a single table or where there are no complex joins, please raise your hand....
Also if you are dynamically generating SQL in your sproc and executing it, you will also have no performance advantage.
First absolutely true thing you've said. That's why most of us argue over and over again against using dynamic SQL.
Sprocs also give developers a container to write cursors which are a bad practice (in SQL Server) since they create temp tables.
And giving developers .NET enables them to write Service Oriented Architecture.... your point?
Additionally the most common stored procedures are usually the most trivial SQL statements (insert, update, delete).
Here again, we have agreement. If you want to talk about MOST simple procedures or TRIVIAL procedures and how they don't need to be procedures at all, I'll support you. You're talking ALL because of SOME. Sorry, that's silly.
As to Security, you have some valid points. It really does come down to how secure do you want your data. Some systems really don't have to worry much, as you state. On the other hand, I'd like to think that my bank is putting every layer of security between my money and a bunch of script kiddies as possible. BTW, I do think you may have gotten this backwards... Constructing a string is what exposed the Oklahoma Prison System database to complete exposure, not using parameterized queries. Actually, if you're going to use dynamic SQL, using parameterized queries, where you, the developer, can ensure the data types and commands issues, is absolutely the right way to go. I do want to note that you are effectively granting us that stored procedures are, in fact, more secure.
Reuse, seperation of responsibilities... eh. I don't see these as major arguments. You can have these points for all the good they do.
Stored Procedures are not object oriented... Neither are databases. Shouldn't you just stop using them and go to file storage? Seriously. Why bother with all this mess, normalization, indexes, constraints... They just slow down development and the app can take care of it all, right? But, oh, you want to take advantage of some of the multi-user aspects that databases hand you, right. So you'll keep them in place just so you don't have to worry about concurrency on your XML files. But then, concurrency will be a HUGE problem when you've got recompiles going on constantly, really poor execution plans that aren't taking advantage of indexes, no method for refactoring data access that doesn't require application deployments (which include financial testing and all kinds of other stuff)...
In short, unless the storage mechanisms change, completely eliminating stored procedures is just flat out stupid. Sorry, but there it is.