stored procedure

Question

In the below procedure I want to set title_id into @v_title_id variable so that I can use it in the procedure. Any ideas thanx in advance. I'm using InnoDB engine type.

DELIMITER //

DROP PROCEDURE IF EXISTS sp_Title_SplitGenres //

CREATE PROCEDURE sp_Title_SplitGenres
(
        p_genre_id          INT,
        p_genre_str         VARCHAR(2000)
)
BEGIN

        SET @v_stringseperator = '::::'   ;

        SET @v_title_id = (select title_id from filmo_title_genre where genre_id = p_genre_id);

        WHILE LENGTH(TRIM( p_genre_str ))  > 0
        DO
         SET @v_curr_Str := (SELECT SUBSTRING_INDEX( p_genre_str, @v_stringseperator, 1 ));
         SET p_genre_str =  (SELECT LTRIM(TRIM(LEADING CONCAT(@v_curr_Str ,@v_stringseperator)   FROM  p_genre_str)) );

        create temporary table filmo_title_genre_temp
        (
             title_id     int(11) NOT NULL,
             genre_id     int(11) NOT NULL,
             sequence_num int(11) NOT NULL default '0',
             PRIMARY KEY  (title_id,genre_id,sequence_num)
        )ENGINE = InnoDB;

        while @v_title_id > 0
        DO

        select title_id,genre_id from filmo_title_genre where title_id = @v_title_id ;
        END WHILE;

        END WHILE;

        REPLACE INTO filmo_title_genre 
        SELECT * FROM filmo_title_genre_temp;

       drop table filmo_title_genre_temp;       

       delete from filmo_title_genre
       where genre_id = p_genre_id;

END //

DELIMITER ;

Answer 1

I'm not much of a mysql person (didn't see that tag til I opened this), but I think you want to use SELECT not SET when trying to set a variable from a select statement. You're cluing the parser into this being a table operation.

SELECT @v_title_id := title_id from filmo_title_genre where genre_id = p_genre_id;