Coding Horror: October 2004 Archives

Coding Horror: October 2004 Archives: Who needs Stored Procedures, anyways?

It's intended as sarcasm, but I believe this Daily WTF entry on Stored Procedures should be taken at face value:

I'm sure we've all heard, over and over, that inline SQL is generally a bad practice, and that we should use Stored Procedures when possible. But let's be realistic for a minute. Who wants to write a stupid stored procedure for every stupid little simple query needed.

Have you ever worked on a system where someone decreed* that all database calls must be Stored Procedures, and SQL is strictly verboten? I have, and this decision leads to incredible development pain:

1. Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.
2. Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
3. Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
4. Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
5. Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

So why use Stored Procedures at all? Conventional wisdom says we do it because:

* Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
* Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
* Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
* Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
* Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

And many people buy into this philosophy, lock stock and barrel:

At just about every talk I give I always try to make several consistent statements. One of which is: ‘Whenever possible use stored procedures to access your data’.

However, there's one small problem: none of these things are true in practice. The benefits are marginal, but the pain is substantial.