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=arch...@jab.org
