It may surprise some that people outside of the SQL Server world (Oracle, DB2) do not use sprocs nearly as often and they apply them differently. I will lay out my argument calling for the end of this 'best practice'.
First let's dissect the main arguments for this best practice, they include the following: performance, security, reusability & separation of duties.
Performance argument surrounds the assumption that stored procedures are precompiled. However this is not always a benefit SQL Server's own Books Online says:
When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.
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. SQL Server stores execution plans for any query sent to it, therefore there is no advantage in this department. If any performance difference is negligible. Also if you are dynamically generating SQL in your sproc and executing it, you will also have no performance advantage. Sprocs also give developers a container to write cursors which are a bad practice (in SQL Server) since they create temp tables. Additionally the most common stored procedures are usually the most trivial SQL statements (insert, update, delete).
Security argument is based on two arguments: granular security access & SQL Injection attacks. The granular security argument is that you don't give users access into the table, you only give them access to execute a stored procedure. My response to this is so what, the user's still have access to insert. Really what is the risk (odds) of a user finding out the database security credentials, understanding the database, and issuing a proper sql statement. Odds are if the user is capable of this then they are capable of executing your sproc. Furthermore many pieces of your security have to fail you for any of this to happen. I call this a long shot.
The SQL Injection is actually a misconception based on the fact people commonly issue parameterized queries as opposed to constructing a string. You can use sprocs and still have SQL Injection vulnerability if you are doing something like this:
cmd.Execute("exec InsertCust 1, 'hello'");
Reusability argument is only relevant if you don't have proper layers in an application to allow reuse from your app tier or business objects. Unless you are letting other applications access your database which is generally not recommended.
Separation of duties assumes that the job of your RDBMS is to be a repository for SQL code. This wasn't it's vision and there's a stronger argument that SQL statements fall more inline with business process and business logic therefore they belong there. If you implement a good ORM strategy then you don't even have to worry about 'hard coding' SQL into your application. Furthermore your app tier and business objects are more likely to have unit tests therefore regression testing at the object level is seamless.
The case against stored procedures
Stored procedures are procedural, not object oriented therefore their extensibility for reuse is minimal. This is why a sproc only architecture is a failed one, it's time consuming for little to no return on your investments. It's common for people to rewrite an entire sproc purely because the original one did not work exactly for their purpose or they do not understand it.
