Fetch last inserted ID form stored procedure in MySQL

Question

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?

Answer 1

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.