MySQL Error Code: 1064 Stored Procedure Syntax Error near END END
I am very new to MYSQL programming, I need to create a stored procedure that adds an animal to the animal table, but need to check that foreign keys exist in the Catagory table (catID) and the Area table (Areaid).
This is what I have so far, but I get an error that says a syntax error next to END END, I tried to put parentheses, but then there are only more errors.
If anyone can help, that would be great!
-- checking if the SP already exists and deletes it DROP PROCEDURE IF EXISTS sp_AddAnimal; -- creates the stored procedure to add animals to the Animal Table DELIMITER $$ CREATE PROCEDURE sp_AddAnimal ( IN p_animalType VARCHAR(30), IN p_weight VARCHAR(6) , IN p_gender VARCHAR(7) , IN p_age VARCHAR(4) , IN p_stock INT , IN p_catID INT , IN p_areaID INT ) BEGIN IF EXISTS (SELECT catID FROM Catagory WHERE Catagory.catID = p_catID) THEN BEGIN IF EXISTS (SELECT areaID FROM Area WHERE Area.areaID = p_areaID) THEN BEGIN INSERT INTO Animal ( animalType , weight , gender , age , stock , catID , areaID ) VALUES ( p_animalType , p_weight , p_gender , p_age , p_stock , p_catID , p_areaID ) ; END END END DELIMITER;
source to share
The syntax for blocks is
IF ... END IF
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
From the link in the comments.
You need to use
IF EXISTS (SELECT catID FROM Catagory WHERE Catagory.catID = p_catID) THEN
IF EXISTS (SELECT areaID FROM Area WHERE Area.areaID = p_areaID) THEN
source to share