title: How to write a stored procedure to insert values in multiple tables
body: <p><img src="https://i.stack.imgur.com/LuP9v.png" alt="Database Schema"></p>
  
  <p>How do I write a stored procedure to add a person record with <strong>multiple addresses?</strong></p>
  
  <p>It is easy if the person has only one address but I'm not sure how to write a stored procedure to add a person with multiple addresses.
  Here is the stored procedure to add a person with one address:</p>
  
  <pre><code> DELIMITER $$
  
   CREATE PROCEDURE `log`.`spAddPerson` (
   IN personID INT,
   IN personName VARCHAR(100),
   IN addressLine1 VARCHAR(45),
   IN addressLine2 VARCHAR(45),
   IN myCity VARCHAR(45),
   IN myState VARCHAR(45),
   IN myCountry VARCHAR(45)
  </code></pre>
  
  <p>)</p>
  
  <pre><code> BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION 
   BEGIN
        ROLLBACK;
   END;
  
   START TRANSACTION;
  
   INSERT INTO person VALUES(personID,personName);
   -- addressid is automatically generated
   INSERT INTO address(Line1, Line2,City,State,Country) VALUES
   (addressLine1, addressLine2, myCity,myState, myCountry);
  
   INSERT INTO personAddress(personID, last_insert_id());
  
   COMMIT;
  
   END
  </code></pre>
  
  <p>The above code works fine. However, I do not know how to handle a person with multiple addresses without writing a separate stored procedure. Is there a simple way to do this?</p>