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 <mo...@fastmail.fm> 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@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=arch...@jab.org
