Stored Procedure compilation question: Doing disparate things in aproc

[JeffRoughgarden]

To minimize the very large number of stored procedures typically
associated with an application, I have gotten in the habit of
combining a select, insert, update, and delete all in one procedure,
and passing an argument to indicate which to use. (I use default
values for all input params to avoid having to declare them for
selects and deletes.) So I'll have just one PersonAdmin proc instead
of PersonGet, PersonInsert, PersonUpdate, and PersonDelete procs

While this is nice for housekeeping, I wonder what the compiler does
with such an architecture,an d I fear the worst. The select returns a
recordset; the others don't.

Is this a bad idea?

If it is, I really wish SQL would permit some sort of user folder
structure in the proc list.

[CptVorpal]

Re: Stored Procedure compilation question: Doing disparate things ina proc

I've seen systems where those types of procs are combined, but the
issue I have with them is that your procedures are no longer small
modular bits. This makes them hard to debug and even more difficult to
combine within other procedures.

I completely agree with you about proc folders, but for now only
strong naming conventions will save us.

[Erland Sommarskog]

Re: Stored Procedure compilation question: Doing disparate things in a proc

JeffRoughgarden (jroughgarden@g mail.com) writes:

To minimize the very large number of stored procedures typically
associated with an application, I have gotten in the habit of
combining a select, insert, update, and delete all in one procedure,
and passing an argument to indicate which to use. (I use default
values for all input params to avoid having to declare them for
selects and deletes.) So I'll have just one PersonAdmin proc instead
of PersonGet, PersonInsert, PersonUpdate, and PersonDelete procs
>
While this is nice for housekeeping, I wonder what the compiler does
with such an architecture,an d I fear the worst. The select returns a
recordset; the others don't.
>
Is this a bad idea?

As everything else: it depends. If you want some users to be able to
retrieve data, but not be able to update, this approach has its
problems.

Personally, I think its normal to have INSERT and UPDATE in the same
procedure, as they tend to have a lot of logic in common. We also have
cases where we house the DELETE in the same procedure, but that
means you have to pass a lot parameters that are not needed for DELETE.

Myself, I would always put the SELECT in a separate procedure.

If it is, I really wish SQL would permit some sort of user folder
structure in the proc list.

In SQL 2005 you can use schemas to create different name spaces. (You
can in SQL 2000 as well, but as schema = owner in SQL 2000, it's quite
messy.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx