Return value from a insert stored procedure

[steve_rm]

Hello,

I have a stored procedure that inserts a new passenger into a database. The fields are name and email. The passengerID is a auto increment. The database I am using is mySQL.

The problem is that I want to return the passengerID when the stored procedure is called. I am not sure if that is possible. Here is my code:
Store procedure in mysql

DELIMITER $$

DROP PROCEDURE IF EXISTS `flightdb`.`spInsertNewPassenger`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertNewPassenger`(_name varchar(50), _email varchar(5) )
BEGIN
	INSERT INTO Passengers ( name, email )
	VALUES ( _name, _email );	
END$$

DELIMITER ;

The stored procedure is called from a java program below:

try
		{
			CallableStatement cs = conn.prepareCall( "{ spInsertNewPassenger( ?, ?) }" );
			
			cs.setString( 1, name );
			cs.setString( 2, email );
			
			cs.executeUpdate(); //I want to return the ID here
			
		}

Can anyone help change the above code so that i can return the ID number.

Thanks in advance,

Steve

[CornedBee]

I think you best add an OUT parameter to the procedure that receives the new ID. (The LAST_INSERT_ID MySQL function can retrieve the ID if it's an auto_increment column.)