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;
+3
source to share
1 answer
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 END IF
instead END
to close
IF EXISTS (SELECT catID FROM Catagory WHERE Catagory.catID = p_catID) THEN
and
IF EXISTS (SELECT areaID FROM Area WHERE Area.areaID = p_areaID) THEN
0
source to share