title: Pass list of values to be inserted as parameter to stored procedure
body: <p>I have this stored procedure in MySQL version 5.7. If I want to create n elements it would be much faster to insert n elements in one statement two times rather than call the stored procedure n times.</p>
  <p>How can I create a stored procedure that takes a &quot;list&quot; of names, a team id and does this?</p>
  <pre><code>CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN data_user_team_id INT)
  BEGIN
  START TRANSACTION;
  INSERT INTO users (users.name, users.type)
  VALUES (name, &quot;team_data&quot;);
  INSERT INTO team_members (team_id, user_id, mod_time)
  VALUES (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP());
  COMMIT;
  END ;;
  </code></pre>
  <p>A stored procedure that creates two elements would look like this:</p>
  <pre><code>CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN name2 VARCHAR(100), IN 
  data_user_team_id INT)
  BEGIN
  START TRANSACTION;
  INSERT INTO users (users.name, users.type)
  VALUES 
  (name, &quot;team_data&quot;),
  (name2, &quot;team_data&quot;);
  INSERT INTO team_members (team_id, user_id, mod_time)
  VALUES 
  (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP()),
  (data_user_team_id, LAST_INSERT_ID()+1, UNIX_TIMESTAMP());
  COMMIT;
  END ;;
  </code></pre>
  <p>Edit:
  There are different ways to tackle this. You could just scrap the SP and copy-paste batch insert code. Rick James presented a complex solution, but I think you can do something simpler, perhaps at some cost in performance (but better than many individual inserts).</p>
  <p>On the caller side you do something like this:</p>
  <pre><code>CREATE TEMPORARY TABLE names (name);
  
  INSERT INTO names
  VALUES
  (name1),
  (name2),
  ...;
  
  CALL create_data_users(1);
  </code></pre>
  <p>And then the stored procedure is like this:</p>
  <pre><code>CREATE PROCEDURE create_data_users(IN data_user_team_id INT)
  BEGIN
  START TRANSACTION;
      INSERT INTO users (users.name, users.type) 
      SELECT name, &quot;team_data&quot; FROM names; 
      SET @num=LAST_INSERT_ID(); 
      INSERT INTO team_members (team_id, user_id, mod_time) 
      SELECT data_user_team_id, @num:=@num+1, UNIX_TIMESTAMP() FROM names;
  COMMIT;
  END ;;
  </code></pre>