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

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