I'd have to agree with Abigail-II on this one. Provided that you are unlikely to move to a DMBS that doesn't support stored procedures any time soon, SPs are as fundemental to decoupling your applications from the database schema, as assessor/mutator methods are to decoupling your objects from their underlying implementation.

They are also the DBMS equivalent of using perls builtin functions in terms of performance. Just as the most efficient way of processing data in perl is to, as far as possible, minimise crossing the boundaries between the interpreted byte-code of your program and the compiled C of the built in functions, so minimising the frequency (and volumes of data, but that isn't always possible) crossing the boundary between your application and the DB is the most effective way of using the DB.

Applications, especially business applications, shouldn't be concerned with how to get the information they need, ie. which tables, how to build the SQL queries etc. They should only need to ask for 'the lastest orders to be processed', or the 'Delivery details for order number nnnnnnn'. Using stored procedures is the best way of isolating the application from these details, especially if there are multiple applications that use each type of business object. If the schema has to change, all the changes are isolated with the DBMS, provided that the SP name and the data required by the application stays the same. I've also used SPs to allow the same application to talk to several DBMSs.

Sure, SPs can be badly written but so can Perl and SQL. Especially SQL! The vast majority of people using SQL have no idea of the costs of using JOINS, referential integrity, foriegn keys, badly chosen primary and secondary indexes. And even if you become expert enough in any given DB to understand these costs. Move to another DB and your knowledge may well be useless.

That's why really good DBA's are way more than just people that ensure the backups are done. Also why a good DBA becomes a specialist in the DMBS that he deals with. Unfortunately the ideal of SQL as a write once, run anywhere database selection and administration language is forlorn.