From: Michael Dykman
Date: February 28 2011 9:36pm
Subject: Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?
List-Archive: http://lists.mysql.com/mysql/224528
Message-Id: <AANLkTi=Ww-w5WDp_=SqC_CkGKGU0zJWH-hRObpby7Rbu@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

One statement will do:

SELECT Min(prod_price) , Max(prod_price), Avg(prod_price)   INTO pl, ph, pa
from products;

 - michael dykman

On Mon, Feb 28, 2011 at 4:30 PM, mos <mos99@stripped> 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 synta=
x
> 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. Tha=
t
> 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(
> =A0 OUT pl DECIMAL(8,2),
> =A0 OUT ph DECIMAL(8,2),
> =A0 OUT pa DECIMAL(8,2)
> )
> BEGIN
> =A0 SELECT Min(prod_price) =A0INTO pl, Max(prod_price) =A0INTO ph, Avg(pr=
od_price)
> =A0INTO pa
> =A0 FROM products;
> END;
>
>
> But I have to break them out into separate Select statements.
>
> CREATE PROCEDURE productpricing(
> =A0 OUT pl DECIMAL(8,2),
> =A0 OUT ph DECIMAL(8,2),
> =A0 OUT pa DECIMAL(8,2)
> )
> BEGIN
> =A0 SELECT Min(prod_price)
> =A0 INTO pl
> =A0 FROM products;
> =A0 SELECT Max(prod_price)
> =A0 INTO ph
> =A0 FROM products;
> =A0 SELECT Avg(prod_price)
> =A0 INTO pa
> =A0 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 variable=
s?
>
> Mike
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
.com
>
>



--=20
=A0- michael dykman
=A0- mdykman@stripped

=A0May the Source be with you.