Re: [GENERAL] stored procedure revisited

Jim Cromie wrote:
[snip Virtues of SP - agreed :) And I have more opinions on the
drawbacks of SP, as expected... ]
>
> Drawbacks of SP:
>
> 1) Secondary BL mechanism - Referential Integrity is generally regarded as
> better. Its declarative, so is easier to use in the query optimizer.

Triggers can certainly be _procedural_.

>
> 1) Sub-Optimal location for Business Logic
>
> Business Logic kinda goes with Business Applications; Apps are the context
> and cause for BL, and probably the most natural place to define it,
> particularly since the App tends to be more OO than RDBMSs..

Having BL in the BA level means there must be a BA for things to work.
Migration path isn't critical for businesses; production databases are
seldomly moved. So this is more of an issue for the application vendors
than the database administrators (they want to sell the product, of
course :P).

>
> 2 concrete contexts from Perl world.
>
> DBI->prepare_cached($sql-cmd): method implies that it is stored for speed.

This statement only works _as_advertised_ w/ databases that have SP.
Note DBD::Pg states that Postgresql does not have a prepare concept
(it's there for compatibility, w/ the complete query sent every time).
That's why SP rocks :)

Regards,

yin-so chen