I use storedprocs exclusively. I write them in sql scripts and precede
each declaration with a matching (if this_storedproc exists, delete
this_storedproc) statement.
Storing them in sql scripts lets me keep them under version control
alongside the code that references them so they are always in sync.
Preceding each with a conditional removal statement allows me to run
the entire script w/o conflicts if i need to make a change, and has the
added benefit of serving as an install script as it describes the
entirety of my stored procs.
Obviously i don't store table definitions with remove statements in
these storedproc script files, or my table data would be cleared out
each time i ran these scripts.
By keeping each table definitions separate, however, i can group my
stored procedure by table or list them all in a single script. In
either case, storedprocs do not maintain any data, so removing them and
recreating them only incurs a (relatively) small cost at update time.
Even changes to the table schema can generally be handled by a few
search/replace queries on my script file when all my storedproc defn's
are laid out linearly in a flat text file. Either that or i use the
find feature of VIM to zip around my script definitions and make the
updates.
Using this method, i find that administration and update time is
minimal.
This seems to address (eliminate?) the pros that you list below and
suggest that storedprocs are, indeed, the way to go.
From the reading that i've done, the more calculation you can move into
the database, the more the server can optimize your query for speed,
and the fewer high-cost database accesses you need to incur in your
application.
cheers,
David.
MattC wrote: Hi,
I'm implementing a new Business Layer in one of our applications. I'm
toying with the idea of placing all the Create, Read, Update and Delete SQL
in the object in question and build a dynamic SQL string using a class
builder.
The pros I can see are:
It reduces the number of stored procs to admin.
Changes to the underlying table schema does not require massive changes to
large numbers of storeprocs