Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
SaintClair

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

Share this post


Link to post
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$$
 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...