stored procedures

[Tom Metro]

Mark Woodward wrote:
>> No matter what you start with, you may want to change it later on.
>> Stored procedures will make that a nasty pain.
>   
> Only if you want to switch databases mid-stream, and no database will be 
> without problems.

I tend to think people are against using stored procedures more because
its the trend these days than for any solid reasons learned through
experience. On the one hand, as Mark mentions, any time you port to
different databases, you're going to need to do a bunch of work at the
storage layer, be it stored procedures or the storage layer of your
application code. On the other hand, if you are dealing with a project
with a high probability of being ported, then as David Kramer mentions,
you've got a better chance of accomplishing maintainable portability by
avoiding stored procedures.

I think David touched on some of the better reasons for not using stored
procedures, such as ease of development. Unless your project is large
enough to have dedicated DBAs writing and maintaining the stored
procedures, the stored procedure language is always going to be a
"second language" to the application developers. It won't be as easy to
remember, review, test, or debug. Will the performance boost of using a
domain specific language be worth it? (I'm not a big fan of introducing
a bunch of limited mini-languages into a project when a well designed
library API can accomplish much of the same clarity without introducing
a new syntax.)


> Where possible, the DBA and the database should provide as much
> "service" to the web code. For instance:
> 
> select shopping_cart_contents($cart);
> 
> The function will return the contents of a cutomers shopping cart. Now, 
> the whole implementation of shopping cart can change without affecting 
> the web design.
> 
> It is a well understood practice to isolate "knowledge of data 
> structure" from the use of the data. Its just another API.

I'm surprised that no one in this thread has mentioned web services,
which is what has largely replaced stored procedures on large systems.
You get a similar separation of application and storage layers, while
sticking with your familiar development language and tools.

I think stored procedures can still be useful for small systems where
porting is unlikely, and they're too small to justify building a middle
layer of web services. (For example, I use MySQL stored procedures to
wedge additional functionality into the way Postfix looks up mailboxes.)

 -Tom

-- 
Tom Metro
Venture Logic, Newton, MA, USA
"Enterprise solutions through open source."
Professional Profile: http://tmetro.venturelogic.com/