>So what are the justifications? What makes a wise stored procedure
>and an unwise stored procedure?
Use them when there is a real benefit to using stored procedures. I will give one example
when I would have used them:
With a CRM solution I once wrote, I wanted to get a list of n randomly selected customers
to issue a survey to. The generation and retreival of the customers was fairly painless,
you simply add ORDER BY RAND() to your query with a LIMIT n clause. The catch is that I
also wanted to add a row to a log table for each of these people to show that thay were
going to be part of this particular random mailing. Now of course at this point these
people were going to be on the client side. The solution is to SELECT the random people
into a temporary table, then mangle the table to be able to INSERT it into the log table.
If I would have had access to stored procedures, I would have had the stored procedure
retrieve the people and both return them to the client app AND INSERT a row to the log,
saving me time and trouble.
One thing to remember is that every stored procedure you execute steals resources that
would otherwise go to handling queries, so you do not necesscarily see a performance
boost by using stored procedures for everything, in fact a MySQL server handling a lot of
stored procedures could very well show poorer performance than a proper n-tier