Getting Error When Creating A Stored Procedure In Chapter 8?

[SaintClair]

Hi,

 

I hope everyone is well.

 

When running the stored procedure for creating select_products below in phpMYADMIN I get the error:   "#1064 - 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 '' at line 10".    When I look at the line, the syntax seems to be correct.  Line 10 is where "ORDER BY name ASC;" lies, and where the error message is stating the problem resides.  Can anyone point me in the right direction?

 

Many Thanks,





 

CREATE PROCEDURE select_products(type VARCHAR(6), cat TINYINT)

BEGIN

IF type = 'coffee' THEN

SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku, 

CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name, 

sc.stock 

FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id 

INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id 

WHERE general_coffee_id=cat AND stock>0 

ORDER by name ASC;

ELSEIF type = 'other' THEN

SELECT ncc.description AS g_description, ncc.image AS g_image, 

CONCAT("O", ncp.id) AS sku, ncp.name, ncp.description, ncp.image, 

ncp.price, ncp.stock 

FROM non_coffee_products AS ncp INNER JOIN non_coffee_categories AS ncc 

ON ncc.id=ncp.non_coffee_category_id 

WHERE non_coffee_category_id=cat ORDER by date_created DESC;

END IF;

END$$

[SaintClair]

The first stored procedure below worked correctly, so my phpMYADMIN is up to date enough to create stored procedures:

 

"DELIMITER $$

CREATE PROCEDURE select_categories (type VARCHAR(6))

BEGIN

IF type = 'coffee' THEN

SELECT * FROM general_coffees ORDER by category;

ELSEIF type = 'other' THEN

SELECT * FROM non_coffee_categories ORDER by category;

END IF;

END$$

 

 

[SaintClair]

Sorry I got it, I left off the DELIMITER at the beginning and the end.  This is resolved.

[Larry]

Excellent. Thanks for letting us know!