Stored Procedures, what are they good for?

We're all familiar with the traditional benefits of stored procedures. For instance, this site, which talks about implementing stored procs in mySQL, lists them nice and succinctly:

1. Better performance Stored procedures are faster because they are pre-compiled SQL code. This reduces the “Compile and Execute” step to just “Execute’ in most cases. Also, only the call to the stored procedure needs to be sent to the server instead of chunks of information – this reduces the information that needs to be sent to the server and acts like a call to a remote procedure. This is an advantage when it comes to code that is called repeatedly. However, the load on the server is another point to consider since most of the processing will now be done on the server.
   
   
2. Easier to maintain Since all the SQL can now be stored on the server, it is easier to make changes to the stored procedure than to a bunch of SQL statements distributed all over the application.
   
   
3. Security Although the use of stored procedures is not by itself a guarantee of security, it can be used to create an environment where applications and users can only access database tables through the stored procedures, instead of giving them direct access to the tables. The benefit is a layer of abstraction.
   
   
4. Optimization When a SQL statement is parsed by the server, it is optimized internally by the server. If a bunch of SQL statements are sent to the server, repeatedly, they have to be optimized each time. The SQL statements in the stored procedure that is in memory have to only be optimized once and an execution plan is created for the SQL statements in the stored procedure.

But, is it as simple as that? Not if you believe Frans. He argues that dynamic SQL is the way to go. He makes some good arguements, for instance, pointing out that for MS SQL Server in particular, stored procs have no special priority over other SQL code, and are in fact compiled at run time (thus countering, to some extent, the performance arguement, although he does ignore the benefit of reduced network traffic).

He also objects to the API that you effectively create with stored procs, which can be a problem when the underlying table changes, as this forces the CRUD procs (at least) to change accordingly, breaking any existing client code.

My take? Having worked on many systems where the DB layer was accessed by many systems and languages, I'd say a fixed API was a good thing. Also, it limits the rewriting of identical (byt error prone) code over and over again.

Aside: many of the arguements for and against ORM tools apply equally well in this case.