Technical . StoredProceduresAreBad

I was reading this article about why Stored Procedures are fine which was a response to yet another article about why Stored Procedures are bad.
So I might as well throw in my two cents' worth. First, a bit of background. I'm a 10-year database administrator. I've administered Microsoft SQL Server, IBM DB/2, Sybase, Oracle, PostgreSQL, and MySQL. I've been a database developer (Perl, PHP, Java, and Powerbuilder), and I've worked in shops that used Stored Procedures extensively, and in other shops where they were used not at all.
The article I've linked above makes this claim:

Mr. Bouma makes the claim that, "changes to a relational model will have always an impact on the application that targets that model". Again, he is correct. Which is why applications should NOT target a model. Applications should have absolutely no knowledge of database schema, including table names, column names, data types, or any other information. This is the role of stored procedures.

Here's another solution for Mssrs. Bouma and Machanic: Put knowledge of the schema into a single application module (written in the same language as your application, if you so desire) in subroutines. You can fix up all your application database modules just as easily as you can a bunch of stored procedures.
Adam Machanic's says that stored procedures are good for security, which I guess is partly true, depending on the RDBMS in question. For MySQL, though, since you can have security down to the column level, I don't really see the advantage of moving security out of the RDBMS proper and into stored procedures. Even still, an abstracted function library in your application's language provides the exact same security model.
If you're using some of the more archaic legacy RDBMSs like Oracle, Sybase, or Microsoft, you don't really have a choice here. Their security model is lacking. Stored procedures are the bandaid applied to the failed heart.
Neither Adam Machanic nor Frans Bouma are really getting it. Hard to blame them. We've got thirty years of broken thinking that brought us to this point. So what's the answer? Why do I think stored procedures are bad?
What stored procedures do really, really well is tie you nearly permanently to a single RDBMS. No wonder Microsoft, Oracle, Sybase, and IBM are all about stored procedures. Their livelihood depends on it. MySQL AB, on the other hand, hasn't really bothered with them up until recently, and yet Google, Yahoo!, and a litany of other high-volume database shops (that run MySQL) have been chugging away quite happily.
To use stored procedures, you need to start using complex and nonstandard functions and looping constructs, for which ANSI SQL appears to have no good mechanism. Before you know it, you're writing complicated logic in bizarre SQL-like language that only works on one RDBMS.
The more you write, the longer it would take to rewrite it in some other language. A language like, say, C, Perl, or PHP. If Oracle calls you up and says "You're gonna be paying 5x what you are today," what is your recourse? Call a lawyer? No thanks, I'd rather be able to just say: "Great. Tomorrow we'll be using your competitor's RDBMS. Have a nice day." (For what it's worth, I hear Oracle allows you to write stored procedures in pure Java. If that's true, and the Java stored procs are fully functional, it's a big argument in favour of allowing developers to write stored procedures in an Oracle shop).
If you write all your "stored procedures" (that is, complicated per-row logic on a resultset) in a standard language like C, Perl, or PHP using a DB connection layer like DBI, you can do all the same complex logic per row of your resultset... but you can do it in an industry-standard language that doesn't change at the whims of a weird company that doesn't care about your well-being. If you do it right, you can use PostgreSQL today, MySQL tomorrow, and Sybase and Oracle through the weekend.
Summary. A list of advantages of writing your "stored procedures" as a function library:

Industry-standard language that everyone knows, not just DBAs

Language that won't change when your RDBMS version does

Language that's portable across RDBMSs and architectures

Speed of algorithms used by a huge community rather than the limited "stored procedure-writing community" (is your RDBMS's quicksort as efficient as Perl's? I'll put money down that says it isn't)

Modularity of DB-related code

Security not weird SQL code, either in RDBMS (MySQL) or application logic (if you do not run MySQL)

Your DB logic routines need not be in the same language as your application, or indeed, limited to a single language

Your DB logic routines could be compiled or script language

Your DB logic routines don't need to run on the same computer as the RDBMS (but can, if network overhead is a concern of yours)

Drawbacks of stored procs:

Vendor lock-in

Algorithms only tested and tuned by hundreds of people, not millions

Odd security paradigms that only DBAs can understand (and yes, I understand them. They're still odd)

Things both stored procedures and application function libraries can do:

Complex logic per-row

DB access is modularised and abstracted away from the application proper

Edit -:- Attach -:- Ref-By -:- Printable -:- More