When I'm working with clients, I hear quite a few that are quite adament about "Only access the database through Stored Procedures."

They list security and performance as their top reasons, as well as the all important "we always did it this way."

I am in the completely opposite side, I want to hand over my database querying to an engine that can take a reasonable request and do something with it. For instance, transform it to a SQL statement and give me the results from the database without me having to write the SQL code. It is just as performant, because there isn't any difference between the caching for stored procedures and parameterized SQL. In fact, one can argue that the query plans for parameterized SQL can be optimized better than the ones for Stored Procedures, since the optimizer is free to recompile the plan based on the expected rows that the query is likely to affect.

The issue of security doesn't bother me because I'm not giving access to my dataase to any Joe User, I'm giving access to the application which (presumebly) I wrote and I trust. SQL Injection is not an issue, of course.

Considerring that an evil developer could just as easily exploit the SP layer to do whatever they want in the database (that is assuming that they aren't the ones writing the stored proceures, of course), I don't see any point in not trusting the application. If this is a database that serves several application, I would argue that the database is not the correct place to put this abstraction and that a web service would be much better.

I believe that this tendecy to use stored procedures as the default mode of operation stems from the times where client server system where all the rage. In those cases, you often had to give the users access to the database. And a knowledgable user would be able to go in and change whatever they wanted. In this scenario, SP are the way of least resistance for securing the application.

These days, I'm not familiar with many classic client / server apps that talks directly to the database, and I would argue venehemtly against this kind of architecture. Under those circumstances, I fail to see what are the advantages of using stored procedures.