title: MySQL Stored procedure for updating two tables (2nd with auto increment value from 1st)
body: <p>I want to update two tables at the same time in my database. One table is for groups, and the other table is for members of groups:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS groups (
      group_id                 INTEGER UNSIGNED AUTO_INCREMENT,
      group_name               VARCHAR(150) NOT NULL DEFAULT '',
      group_created            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (group_id)
  ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
  
  CREATE TABLE IF NOT EXISTS group_members (
      group_mem_user_id        INTEGER UNSIGNED NOT NULL,
      group_mem_group_id       INTEGER UNSIGNED NOT NULL,
      group_mem_role           TINYINT DEFAULT 1,
      group_mem_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      CONSTRAINT group_mem_pk PRIMARY KEY (group_mem_user_id, group_mem_group_id),
      FOREIGN KEY (group_mem_user_id) REFERENCES user (user_id),
      FOREIGN KEY (group_mem_group_id) REFERENCES groups (group_id)
  ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
  </code></pre>
  
  <p>I want to use a stored procedure to create an entry in <code>group</code> and create an entry in <code>group_members</code> with the id that was just created for the group.</p>
  
  <p>I know how to do this on the server (I have a java server and I'm using Spring's JdbcTemplate to make calls to the database) but I thought it would be better and more efficient to do this in a stored procedure.</p>
  
  <p>The two individual queries are (im using prepared statements):</p>
  
  <pre><code>INSERT INTO groups (group_name) VALUES (?)
  </code></pre>
  
  <p>and </p>
  
  <pre><code>INSERT INTO group_members (group_mem_user_id, group_mem_group_id, group_mem_role) VALUES (?,?,?)
  </code></pre>
  
  <p>But I'm not sure how to merge these into one stored procedure.</p>
  
  <pre><code>DELIMITER //
  
  DROP PROCEDURE IF EXISTS create_group //
  CREATE PROCEDURE create_group(
      #in/out here
  )
  BEGIN
      #no idea
  END //
  
  DELIMITER ;
  </code></pre>
  
  <p>Ideally I would like it to return some value describing whether the operation was sucessful or not.</p>