Jump to content
Larry Ullman's Book Forums

Getting Error When Creating A Stored Procedure In Chapter 8?


Recommended Posts

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$$
Link to comment
Share on other sites

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$$
 

 

Link to comment
Share on other sites

 Share

×
×
  • Create New...