Why do stored procedures limited to Select stmt to 1 OUT parameter?

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=gcdmg-mysql-2@m.gmane.org

Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?

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 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@gmail.com

=A0May 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?

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 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=mdykman@gmail.com
> >
> >
>
>
>
>--
> - michael dykman
> - mdykman@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=gcdmg-mysql-2@m.gmane.org