CODEWITHSUNDEEP
mysqlstored-procedures
Greiver
Greiver

Reputation: 15

MySQL 5.1 Stored Procedure Error - Declaring Variable

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.

Upvotes: 0

Views: 2791

Answers (1)

krokodilko
krokodilko

Reputation: 36097

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.

Upvotes: 2

Related Questions