Bill (pstscrpt) wrote in computerscience,

  • Music:

Stored procedures

This is more information systems than computer science, but it's not like there's been a flood of traffic here lately. Anyway, I just wrote this in response to this article, but I think it stands alone decently well.

I think by now anyone who's paying attention realizes that parametrized queries have the same advantages as stored procedures with regard to parsing/execution plans and SQL injection. The permissions advantages don't really exist anymore, either, since everyone is just connecting with one ID these days and setting up their own security systems. That doesn't mean there aren't still good reasons for using stored procedures:

1. It's frequently a lot less work for the DB server to just return answers or run a small job on its own than for it to return enough information to another tier for it to be done elsewhere. This may be bandwidth, or it may just be knowing when it can find an answer with an index and skip the real table.
2. Similarly, when programming stored procedures, you can just use the data you need, since it's right there, without worrying about what you do and don't need to pull between layers. You also don't have to have to make any distinction between data that's native to the environment you're working in and data that's using an interface to a foreign system (granted, an ORM gets you that benefit, too).
3. While stored procedures do require other people (DBAs) to get involved in a release, they also frequently allow problems to be fixed without rolling out a new version of the entire system.
4. Oracle isn't as good at this, but with Transact-SQL, you can frequently get jobs done with a lot less set-based relational code than the equivalent object-oriented approach would require.
5. In a stored procedure language, you always have good support for Nulls.
6. If you put *all* of your business logic in stored procedures, that actually gives you a pretty good MVC breakdown.
7. The database is the only layer that's *always* present, so doing work in stored procedures gives you a lot better resources when you're doing ad-hoc, one-time jobs.
8. If you change front-end or middleware architectures or languages, your procedures are still good.

As for downsides, yes you have vendor lock-in (so stored procedures are largely ruled out if you're writing software for other companies to run at their own sites), and yes, many programmers just aren't very good at SQL. Also, you then have to deal with a database interface (and probably manual code check-ins), as well as your IDE. And automated testing becomes a much more do-it-yourself affair.
  • Post a new comment


    default userpic