Fetch last inserted ID form stored procedure in MySQL

mysql

Solution

You need to use SET statement. For example -

Table:

CREATE TABLE table1(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  column1 VARCHAR(10),
  column2 VARCHAR(10)
);

Procedure's body:

BEGIN
  INSERT INTO table1(column1, column2) VALUES ('value1', 'value2');
  SET out_param = LAST_INSERT_ID();
END

Note, that ID field is not specified in INSERT statement. This value will be inserted automatically; and of course, this ID field must have AUTO_INCREMENT option.

Problem

How to fetch last inserted id? ``` CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insertzonemsg` (IN User_Id INT(10), IN zid INT(10), IN update_titles VARCHAR(50), IN textarea1s TEXT, IN filupload1s TEXT, IN audio1s VARCHAR(100), OUT out_id INT(10)) BEGIN INSERT INTO zone_message_master **(user_id,ZoneID,update_title,textarea1,filupload1, audio1,LastUpdate)** VALUE **(User_Id,zid,update_titles,textarea1s, filupload1s,audio1s,NOW());** SELECT **id** as **out_id** FROM **zone_message_master** LAST_INSERT_ID(); END$$ ``` I need to return last inserted ID as `out_id` form the Table `zone_message_master`?

Original source