Re: Writing most code in Stored Procedures

On 8/15/07, Rohit <rpk(dot)general(at)gmail(dot)com> wrote:
> I have few queries regarding the use of Stored Procedures, Functions
> and Triggers in an RDBMS.
>
> (1) When to use Stored Procedure? Writing an INSERT query in a Stored
> Procedure is better or firing it from the application level?
>
> (2) Can a Trigger call a Stored Procedure?
>
> (3) What type of code must reside in application and what type in
> RDBMS?
>
> (4) Is it faster to work at application level or at the database level?

I'm fairly hardcore about keeping as much business logic as I can in the
database. In fact, I only do SELECTs from the application, and usually
via Views. All inserts, updates and deletes are via procs. I'm a
proponent of separating application code from presentation and
application code from database code. And HTML from layout style, for
that matter.

In addition to the other reasons you've gotten:

It lets me blackbox commonly used functions, such as a function to
insert a normalized demographic record for a customer, an organization,
a user, a guarantor, a physician, etc.

It isolates database logic so it can be debugged separately from the
application.

It reduces the application's vulnerability to SQL injection, especially
if another developer (never me <g>) forgets to massage potentially
tainted user input.

Another is because I typically do my web application programming in PHP5
but the offline scripts in Perl. Both can call the same stored
procedures so I don't have multiple copies of database code to maintain
and debug.

Another is because I want transactions to start and end in the database,
not in external application code which might crash before a COMMIT.

Another is because I'm a freelancer and generally hand off my
applications to the client's tech department, which is often a junior
level grasshopper who knows just enough SQL to be dangerous. Using
stored procedures raises the bar on what they need to know about RDMBSes
before they start hacking working code.

And, yes, it's faster. Particularly if business logic decisions have to
be made in context with a transaction.