Tuesday 9 November 2004 — This is over 18 years old. Be careful.
Rob Howard says stored procedures are good. Frans Bouma rebuts with stored procedures are bad. Many many comments ensue.
In my experience, this debate comes down to a mindset. If you are fundamentally database-focused, you will like stored procedures. If you are fundamentally application-focused, you will not. Generally, requirements like portability and maintenance costs will outweigh personal preferences any way. For example, if your application has to run on more than one database, stored procedures will be very expensive to use (because they will have to be re-written for each database platform). Personally, I find writing code in pre-paleolithic SQL extensions to be absolutely abhorrent, so I don’t like stored procedures. Some day, maybe I’ll work on a project where their benefits outweigh their disadvantages.
It depends on what type of portability you need. If you are going to be writing multiple database front-ends (e.g. web front-end in PHP, C++ front end for the desktop), then you'll want to separate the front-end from the database design with a clear API. Stored procedures are the way to do this.
If, on the other hand, you'll only be writing one front-end, it makes more sense to leave your options open rather than lock yourself into one database.
Either way, you're going to be the subject of lock-in - if you use procedures it'll be the database you are locked into, and if you don't, it'll be the front-end you are locked into. Which is the better approach isn't a matter of technology but the problem at hand.
Doubt it. Hasn't happened to me yet.
Also, parameterized SQL pretty much kills any of the performance argument. Which was nonsensical to begin with; if I'm not gonna write my entire app in assembly language (for what, I hope, are obvious reasons), why would I want to write my entire app in *database* assembly language?
>Either way, you're going to be the subject of lock-in
I don't think so. Not if my API is a web service. There are so many ways to build a data abstraction layer without the many downsides of procs.
Now, like all assembly language, there are places where procs might make sense. I don't categorically rule it out. But it is a specialized solution for specialized problems. What's really aggravating is all the bad advice people are giving out about this, which is, even more aggravatingly, almost always presented as an generic extreme-- you better use procs, and all procs, otherwise your database performance will be crap! It's just not true.
- it does take many more likes of PL/SQL (Oracle) to do things I can do procedurally in .NET using built in objects, or better language constructs. It's an extremely primitive language.
- PL/SQL may not crash the database, but you can (and will, unless you do use Oracle's crazy error handling) get some extremely cryptic low-level errors back, and it's very hard to debug or step through these code blocks.
I think it's a perfectly apt comparison, although I admit assembly language is an extreme case.
Add a comment: