CODEWITHSUNDEEP
mysqlsqlstored-procedures
Asis
Asis

Reputation: 367

MYSQL Stored procedure not behaving as expected

I'm trying to create a stored procedure that will instert a record into the database and return the newly created id. If a record with the same name already exists, it should return the id of that record. This is what I have so far, it works for newly inserted records, but returns nothing if the record already exists.

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tag`(IN `tag_name_in` VARCHAR(255), OUT `tag_id_out` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
BEGIN 
    INSERT INTO tag (name) VALUES (tag_name_in); 
    IF ROW_COUNT() = 1 THEN 
        SET tag_id_out = LAST_INSERT_ID(); 
    ELSE 
        SELECT id INTO tag_id_out FROM tag WHERE name=tag_name_in; 
    END IF; 
END

Upvotes: 0

Views: 47

Answers (1)

Akina
Akina

Reputation: 42844

I do not see the reason to use LAST_INSERT_ID() at all.

CREATE 
DEFINER=`root`@`localhost` 
PROCEDURE `insert_tag`(IN `tag_name_in` VARCHAR(255), OUT `tag_id_out` INT) 
NOT DETERMINISTIC 
NO SQL 
SQL SECURITY DEFINER 
BEGIN 
    INSERT IGNORE INTO tag (name) VALUES (tag_name_in); 
    SELECT id INTO tag_id_out FROM tag WHERE name=tag_name_in; 
END

Due to the question text tag (name) is defined as UNIQUE (maybe even PRIMARY KEY).

Upvotes: 1

Related Questions