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







All Articles