Re: pros and cons of using stored procedures

From: Matti Teppo <matti.teppo_at_nospam.deio.net>
Date: Wed, 19 Sep 2001 08:56:17 +0200
Message-ID: <3BA84191.F2BBED37_at_nospam.deio.net>


My experience is from Sybase environment. Here are some experiencfes and tips.

One good thing with stored procedures is that you can install them on-the-fly. Eg in a client-server system, if the SQL is embedded in the application, you must upgrade every box where the application is running when you change the SQL. If you can implement a change or bug fix by editing the sp only, it is easy to install the enhanced sp without touching the client machines at all. This is true as long as the input parameters and result set structure(s) of the procedure are not changed so that a change in the calling appication is needed.

Procedures can call other procedures. This can be useful in some specific situations. You can eg have different calculation formulas for a single logical purpose by letting clients call a single procedure, which in turn - according to a configuration stored in the db - calls other procedures where the different algorithms are implemented.

Managing the sp library is important to keep it under control. If every developer just writes his/her procedure without checking in the sp library first, you soon have several procedures doing nearly the same thing. No good.

Take care not to put any such logic in sps that can be implemented with constraints or key assignments.

Write the procedure documentation after the CREATE PROCEDURE clause, so it can be read with sp_helptext directly in the database.

If you have several databases in the server and you load one of them from a dump, you have to recompile all procedures that refer to the loaded database. Otherwise they may perform incorrectly.

Matti Received on Wed Sep 19 2001 - 08:56:17 CEST

Original text of this message