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

Hi Johan,

Johan De Meersman wrote:
> as a totally off-topc question, wouldn't something along the lines of
> LIMIT COUNT(*)/2, 1 do that trick?
> 
> On 4/15/10, Rhino <rhino1@stripped> wrote:
>>
>> ...snip...
>> For example, suppose you had to determine the median grade for a test.
>> ...
> 
> 

That would require running the query twice as LIMIT only accepts numeric 
literals. For a large dataset, that would destroy the efficiency of the 
stored procedure. It's more efficient with MySQL to capture the value in 
a temporary table, count those temporary results, then create a LIMIT 
query using the prepared statement syntax (dynamic SQL) against the data 
in the temp table.

http://dev.mysql.com/doc/refman/5.1/en/select.html
####
The LIMIT clause can be used to constrain the number of rows returned by 
the SELECT statement. LIMIT takes one or two numeric arguments, which 
must both be nonnegative integer constants (except when using prepared 
statements).
####

The above process could very easily be encapsulated by a stored 
PROCEDURE (but not by a stored FUNCTION) so that you would not need to 
implement it in your client code. Unfortunately the stored functions are 
not allowed to use prepared statements, yet.

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html
####
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be 
used in stored procedures, but not stored functions or triggers.
####


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