List:General Discussion« Previous MessageNext Message »
From:mos Date:February 28 2011 9:43pm
Subject:Re: Why do stored procedures limited to Select stmt to 1 OUT
parameter?
View as plain text  
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 <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 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=1
> >
> >
>
>
>
>--
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.

Thread
Why do stored procedures limited to Select stmt to 1 OUTparameter?mos28 Feb
  • Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?Michael Dykman28 Feb
    • Re: Why do stored procedures limited to Select stmt to 1 OUTparameter?mos28 Feb