List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:February 28 2011 9:36pm
Subject:Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?
View as plain text  
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 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