At 03:36 PM 2/28/2011, Michael Dykman wrote:
One statement will do:
SELECT Min(prod_price) , Max(prod_price), Avg(prod_price) INTO pl, ph, pa
from products;
- michael dykman
Michael,
Brilliant! Thanks. :-)
Mike
On Mon, Feb 28, 2011 at 4:30 PM, mos <mo...@fastmail.fm> wrote:
> I want to have a stored procedure that returns 5 columns from a table
and do
> some calculations on it. But when I try it, it complains there is a syntax
> error on the 2nd "INTO" clause.
> It appears I can have only 1 INTO clause per SQL statement. That means I
> have to execute 5 different SQL statements to get all of the results. That
> to me is incredibly inefficient.
>
> Is there any way to avoid this?
>
> I'd like to be able to do this (only 3 OUT parameters in this example):
>
> CREATE PROCEDURE productpricing(
> OUT pl DECIMAL(8,2),
> OUT ph DECIMAL(8,2),
> OUT pa DECIMAL(8,2)
> )
> BEGIN
> SELECT Min(prod_price) INTO pl, Max(prod_price) INTO ph,
Avg(prod_price)
> INTO pa
> FROM products;
> END;
>
>
> But I have to break them out into separate Select statements.
>
> CREATE PROCEDURE productpricing(
> OUT pl DECIMAL(8,2),
> OUT ph DECIMAL(8,2),
> OUT pa DECIMAL(8,2)
> )
> BEGIN
> SELECT Min(prod_price)
> INTO pl
> FROM products;
> SELECT Max(prod_price)
> INTO ph
> FROM products;
> SELECT Avg(prod_price)
> INTO pa
> FROM products;
> END;
>
> Not only is this slower, but I also run the risk of the prices being
> modified between the Select calls.
> Is there a way around this? Do I have to resort to using session variables?
>
> Mike
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
