title: How to select values matching a variable number of parameters in a MySQL stored procedure
body: <p>I have a requirement to write a MySQL stored procedure (called from .NET) that searches a table of <code>stoppoints</code> and allows me to specify a number of possible <code>stopMode</code> values to match against.</p>
  
  <p>In other words:</p>
  
  <pre><code>CREATE PROCEDURE getActiveStoppoints(
  IN NamePrefix VARCHAR(100),
  IN StopModeMatch1 TINYINT(4),
  IN StopModeMatch2 TINYINT(4),
  IN StopModeMatch3 TINYINT(4),
  IN StopModeMatch4 TINYINT(4),
  IN StopModeMatch5 TINYINT(4)
  )
  BEGIN
  
  -- Return all records matching
  SELECT sp.* FROM stoppoints sp
  WHERE (sp.name LIKE CONCAT(NamePrefix, '%')
  AND
  (
  (sp.stopMode = StopModeMatch1) OR 
  (sp.stopMode = StopModeMatch2) OR 
  (sp.stopMode = StopModeMatch3) OR 
  (sp.stopMode = StopModeMatch4) OR 
  (sp.stopMode = StopModeMatch5) 
  )
  ;
  
  END
  </code></pre>
  
  <p>This approach seems horribly brittle - for example, what if I needed to pass in 6 possible <code>stopMode</code> values, or even 600?  And what happens when I have two other columns I'd like to match against in a similar way?</p>
  
  <p>What other possible ways are there to achieve this?  Can I pass an array into the stored procedure, for example?   </p>
  
  <p>I initially tried this by passing in a comma-separated list of values in a <code>VARCHAR</code>.  I ended up utterly frustrated with this approach because:</p>
  
  <ul>
  <li>Using <code>FIND_IN_SET</code> to match against comma-separated strings doesn't use any indexing, so performance is terrible and it's therefore not a valid solution.</li>
  <li>Creating a prepared SQL statement with <code>PREPARE</code>, <code>EXECUTE</code>, <code>CONCAT</code>, etc.  felt brittle and not very performant either.  Firstly, I'd need to deal with putting quotes around the values, if matching against strings. And also I assume that the query plan would have to be re-created each time the stored proc was run?</li>
  <li>Trying to split the CSV values into a temporary table, then using a subselect  <em>does</em> work but feels very hacky. Plus when you try to separate this out into a stored procedure, you cannot return a table/rows from a stored procedure; instead you must remember the temporary table name and call the stored proc first.  Which doesn't scale beyond use for just in one column.</li>
  </ul>
  
  <p>Please believe me when I say I have spent several hours researching this problem to no avail.  How does one achieve this in MySQL, or is it simply not designed for this kind of stored procedure?</p>