I disagree with many aspects of this post.

First, on performance. The scenario you describe where performance is hindered by a stored procedure only occurs when different parameters result in wildly different distributions of data. As an example, say we have a stored proc which accepts a date range within which records should be fetched.

A small date range will yield a small number of records, whereas a large date range could result in a very large number of records. The query plan for small sets of records and large sets of records is very different in terms of how joins are constructed... so if the small plan is used when a large set of data is requested, it will be inefficient.

In the majority of cases, this does not occur. For the most part, the precompiled aspect of the stored proc will result in much faster execution time than in compiling every time. For those stored procedures which are affected by this data distribution problem, you simply set WITH RECOMPILE and the stored proc recompiles each time as a normal statement would, and the problem goes away. Overall, performance is improved the majority of times that you don't need to use this feature.

Your next arguments about performance and cursors are irrelevant. That's an issue with coding practices not the technology. Cursors can be written anywhere.

You have more of a point about security, I still feel more comfortable having total control over entry and exit points of the application though.

Regarding reusability... this is an important reason to use stored procedures. Most of the projects we do have multiple sources connecting to the database, such as website, a windows service, an externally facing web service, and so on. For operations which rely on database processing, the only sensible thing to do is to make this code usable for all entry points.

A good example of this would be using SQL encryption and opening an encryption key, reading and decrypting the data, and closing the encryption key. There is no reason why this shouldn't be done at the database level.

Separation of duties however is the big one. This is where I very strongly disagree with you. Simply because they are not "extensible" doesn't mean that separation of duties isn't important.

In fact, if you think of stored procedures as interfaces, the "it's not OO" argument fails immediately. The database is an object, and having direct table access to the database is the same as one object (the application) being able to dig around inside another objects internal structure and workings, a clear violation of the encapsulation principle of object-oriented programming. Using stored procedure "interfaces" to encapsulate the private data held in the database is the very essence of OO programming. It is clearly and perfectly object oriented.

Stored procedures allow you to have a well-defined application / database layer. As an application is used over time, databases accumulate more and more data. It becomes necessary to start tweaking the database by denormalising, creating indexed views and so forth.

With a stored procedure driven database, you can make arbitrary changes to the underlying database structure transparently. Multi-level joins can become a single indexed view. A calculated field can become a denormalised field. A single huge table can become a partitioned table. A whole series of queries computed on the fly may now be served directly from a newly created reporting database, all without your application having to know a thing about any of it.

If you have baked all of this logic into your application code, good luck is all I can say. Even with a well architected application, the developing / testing / deploying cycle of these changes is going to be very, very expensive, with the increased risk of bugs that modifying a stable application inherently brings.

Having the stored procedure layer to work with gives you an additional layer of abstraction and flexibility. It allows the application to communicate with an abstract datasource of arbitrary structure, so long as these interfaces exist.

Overall I don't accept that this particular change is inevitable nor that I will "have to prepare myself for trends". The only trends I see here are cowboy application developers thinking they should have their sticky fingers deep in the database pie, and that's not a trend I ever intend on following. Note, I am primarily a developer though I am a qualified DBA as well.