From: Shawn Green
Date: April 15 2010 1:44pm
Subject: Re: When to use Stored Procedures
List-Archive: http://lists.mysql.com/mysql/221250
Message-Id: <4BC71833.8040008@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tompkins Neil wrote:
> Hi,
> 
> I've used mainly of the older versions of MySQL.  However am looking to port
> a application across to MySQL 5.  My question is when would one decide to
> use a Stored Procedure over a query written at the application level ?
> 

The decision to encapsulate a particular process or query within a 
stored procedure is usually based on your business needs.

* Common queries that only change by parameters are good candidates

* Complex multi-step queries are good candidates

* If you need an unprivileged user to make a change to a sensitive 
table, you can avoid some security problems by wrapping that process in 
a stored procedure.  For example, maybe part of your internal HR 
processes is an application that allows people to update  their phone 
numbers but can't be allowed to see the private information in an 
employee's database record. You could create a privileged routine called 
change_phone_number() that could do that without giving the application 
(or another user) the rights to manipulate that table directly.

Does this help?

-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN