0

Running into an error when trying to crate a stored procedure in MySQL (5.1). Whenever I try and run this SQL script, I am presented with:

Error 1064 (42000) At line 3: You have an error in your SQL syntax. Near 'DECLARE checkExists INT; SET checkExists = 0; SELECT count(*) INTO c' at line 29

From what I understand, declaring a variable this way should be acceptable. I have included the SQL script below.

DELIMITER //

CREATE PROCEDURE add_movie(
IN movieTitle VARCHAR(100),
IN movieYear INT,
IN movieDirector VARCHAR(100),
IN movieBannerURL VARCHAR(100),
IN movieTrailerURL VARCHAR(100),
IN starFirstName VARCHAR(50),
IN starLastName VARCHAR(50),
IN starDateOfBirth DATE,
IN starPhotoURL VARCHAR(200),
IN genreName VARCHAR(32),
OUT movieAdded INT,
OUT starAdded INT,
OUT genreAdded INT,
OUT movieStarLinkAdded INT,
OUT movieGenreLinkAdded INT)

LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'Adds Movie, Star, Genre and respective links to DB if they do not exist'

    BEGIN
        SET movieAdded = 0;
        SET starAdded = 0;
        SET genreAdded = 0;
        SET movieStarLinkAdded = 0;
        SET movieGenreLinkAdded = 0;

        DECLARE checkExists INT;
        SET checkExists = 0;

        SELECT count(*) INTO checkExists FROM movies m WHERE m.title = movieTitle;
        IF(checkExists > 0) THEN
                INSERT INTO movies(title, year, director, banner_url, trailer_url)
                VALUES (movieTitle, movieYear, movieDirector, movieBannerURL, movieTrailerURL);
                SET movieAdded = 1;
        END IF;

        SET checkExists = 0;

        SELECT count(*) INTO checkExists FROM stars s WHERE s.first_name = starFirstName AND s.last_name = starLastName;
        IF(checkExists > 0) THEN
                INSERT INTO stars(first_name, last_name, dob, photo_url)
                VALUES (starFirstName, starLastName, starDateOfBirth, starPhotoURL);
                SET starAdded = 1;
        END IF;

        SET checkExists = 0;

        SELECT count(*) INTO checkExists FROM genres g WHERE g.name = genreName;
        IF(checkExists > 0) THEN
                INSERT INTO genres(name)
                VALUES (genreName);
                SET genreAdded = 1;
        END IF;

        SET checkExists = 0;

        SELECT count(*) INTO checkExists FROM stars_in_movies sm, stars s, movies m 
        WHERE m.title = movieTitle AND s.first_name = starFirstName
        AND s.last_name = starLastName AND sm.star_id = s.id AND sm.movie_id = m.id;
        IF(checkExists > 0) THEN
                INSERT INTO stars_in_movies(star_id, movie_id)
                VALUES(
                    SELECT s.id, m.id FROM stars s, movies m WHERE s.first_name = starFirstName
                    AND s.last_name = starLastName AND m.title = movieTitle);
                SET movieStarLinkAdded = 1;
        END IF;

        SET checkExists = 0;

        SELECT count(*) INTO checkExists FROM genres_in_movies gm, genres g, movies m
        WHERE m.title = movieTitle AND genre.name = genreName
        AND gm.movie_id = m.id AND gm.genre_id = g.id;
        IF(checkExists > 0) THEN
                INSERT INTO genres_in_movies(genre_id, movie_id)
                VALUES(
                    SELECT g.id, m.id FROM genres g, movies m
                    WHERE g.name = genreName AND m.title = movieTitle);
                SET movieGenreLinkAdded = 1;
        END IF;
    END //
DELIMITER ;

If someone could help me understand what I am doing incorrectly here, I would greatly appreciate it. Thank you.

1 Answer 1

2

Move all DECLARE statements to the start of a BEGIN .. END block, next to BEGIN.

See documentation: https://dev.mysql.com/doc/refman/5.6/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, that fixed that error. Totally missed that.
@Greiver Please mark the answer as accepted by pressing the check mark next to it if it solved your problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.