At 03:40 PM 31/05/2004, you wrote:

><Sorry to repost but I had my dates all wrong>
>
>Hi
>
>I have a question, which I hope is not too off-topic.
>We are designing a system and have to decide whether to add as much of the
>code as possible as stored procedures. Or should we stay away from stored
>procedures as much as possible. I am very much pro SP. Can anyone tell me if
>there is a study/white paper/faq/whatever that explains why or why not to
>take either of the approaches.
>
>Regards
>Alfred Thomas

Reasons to choose stored procedures :

1. Speed. Even if you are running the DBMS on the same machine as the
application, your code will run considerable faster - rough tests that I
have done show that a SP runs about 30% faster than a query, and about 70%
faster than direct table access (for example, in Delphi, using a TTable
component) It's easy enough to set up a test for yourself to show this.

2. If you're running over a network, a SP always is faster, because it
returns the exact result set, and not any more than that.

3. Controllability : if, for example, you have a function that calculates
Tax based on a table, and the way of working out the tax must change (for
example, in another country) then you must change every client program.
(This holds true for any business rule) If you are using a SP, all you have
to do is change the SP code, and the new calculation will show results in
all client software.

Regards

Tim