Mysql Stored Procedure OUT variable return NULL

mysql, stored-procedures

Solution

You are not using the out parameter (`item_id`). Instead, you're feeding a local variable with a similar name (`@item_id`). Try this:

CREATE PROCEDURE `sp_item`(
    IN name VARCHAR(255),
    OUT item_id INT(11)
)
BEGIN   
    START TRANSACTION;
    INSERT INTO `item` (`name`) VALUES(name);   
    SET item_id := LAST_INSERT_ID();
    COMMIT;
END$$

Problem

I am using below sample stored procedure in my application: ``` DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`sp_item`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_item`( IN name VARCHAR(255), OUT item_id INT(11) ) BEGIN DECLARE item_id INT DEFAULT 0; START TRANSACTION; INSERT INTO `item` (`name`) VALUES(name); SET @item_id := LAST_INSERT_ID(); COMMIT; END$$ DELIMITER ; ``` When i execute this procedure using:- ``` CALL sp_item("TEST1",@item_id); ``` and fetch the last inserted id using:- ``` SELECT @item_id; ``` Then i get NULL as a result however the records is inserted successfully into database. I could not find any relevant help after Googling. What i am missing here?

Original source