Subject Re: Good DB design: When to use stored procedures?
Author Doru Ilasi
Hi Zd,

--- In, "Zd" <toldy007@...> wrote:
> Hi,
> I've read in the FB papers that using stored procedures is highly
recommended because it optimizes network traffic and makes query
execution faster.

Yes, this is true. Thin client(or "fat" server) is meant to process as
much as you can on the server side. This also offer you a big
advantage as your client will be as "empty" of business logic as it gets.

> The question is:
> I have over 50 tables in my DB and a lot of different queries
acessing them in different ways. If I started making stored procedures
for each dynamic query I'm executing now from my program, I'd end up
with 200 stored procedures at least.

The number of tables is not an relevant factor for such a decision.
The final scope of the application and the client is. Many developers
prefer to do such procedures if they go on n-tier client architecture.

> So is it a good idea to put EVERYTHING into stored procedures (like
even a simple update or insert into statement which puts a few rows to
the DB)?

In my personal opinion : no. It not worth the work. Give to your users
access rights (grant them permissions according to the scope), use
triggers (and call stored procedures if needed) to process data and
use stored procedures for complex reports. If access rights schema
will require fine grained rights consider running dummy(empty)
procedures to achieve the goal.


> Thanks:
> Zd
> [Non-text portions of this message have been removed]