Re: Working mostly with Stored Procedures in RDBMS.


"Rohit" <rpk.general@xxxxxxxxx> wrote in message
news:1187179827.836021.238980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?


Some of the answers depend upon the RDBMS. Some are way too general to give
a definitive answer. (1) is basically a Config Mgmnt issue. (2) in Sybase
ASE a trigger can execute an SP. I believe most other RDBMSs have this
capability. (3) there is no "must" SQL-wise. (4) Ambiguous question, but,
in general, RDBMS set-processing is faster than RDBMS row-processing which
can be faster than Client row-processing (owing to greater network IO).

I'm a DBA and IMO SPs offer more flexibility. If you find a
poorly-executing SQL statement which code do you want to test and change? A
stored procedure, an App Server application, or an application on multiple
clients? The last is generally the worst. How do you want to examine
schemas versus app SQL (especially with a design tool)? If most of it is in
the DB it's easier - although some tools can handle the DB and clients
pretty well. If there are argument/parameter changes then it can be tough
with any of the options.


.

Relevant Pages

  • Re: BLL & DAL: How are they different?
    ... stored procedure names which are used in the DAL. ... So long as your DAL is capable of interfacing with SQL Server (either because that's the only RDBMS it supports or because it uses a factory pattern), you should simply be able to drop your DAL into both projects without modification. ... foreach (SqlParameter objSqlParameter in plstSqlParameters) ...
    (microsoft.public.dotnet.framework.aspnet)
  • Working mostly with Stored Procedures in RDBMS.
    ... and Triggers in an RDBMS. ... Writing an INSERT query in a Stored ... Can a Trigger call a Stored Procedure? ...
    (comp.databases)
  • Re: BLL & DAL: How are they different?
    ... And what if you aren't a stored procedure person...? ... the DAL code only, right? ... Depends on the RDBMS but, for SQL Server, I use the SqlParameters ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Retrieving Stored Procedure Returns
    ... >CallableStatement after executing a Stored procedure. ... The RDBMS is MS ... the name spGetNextBatch makes the think there's an actual return value ...
    (comp.lang.java.programmer)
  • Re: chooses not to generate code at all
    ... >>>DBMS is so that the DBMS engine can execute it when triggered by some ... If there is no trigger, ... Stored procedure is one thing. ... > If the procedure execution is not triggered by DBMS ...
    (comp.object)