syntax error in mysql

Question

I am trying to create a stored procedure in MYSQL. Below is my code which is giving syntax error. Can anyone please help me.

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;

...and the error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), ' at line 1

Answer 1

Try changing the MySQL delimiter, which can be interpreted by the client (command line or otherwise) before the procedure definition can be finished.

Try this:

DELIMITER //
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//

DELIMITER ;