title: MySQL Stored Procedure with Insert Select and user variable
body: <p><strong>Problem:</strong>
  I have a MySQL stored procedure and I want to reset a user variable to 0 each time the procedure is called, but the variable seems to be remembering its value from each previous run and I cannot initialize it to zero.</p>
  
  <p><strong>Details:</strong> I have a data table (called <code>data</code>) which I read from and then generate records in a 1-to-1 correlation in another table (called <code>results</code>). I do this in a stored procedure that utilizes an Insert Select statement. I also use a user variable <code>@mycount</code> which increments with each row.  Here is a simplified version of my code:</p>
  
  <pre><code>DELIMITER //
  CREATE PROCEDURE doSomething
  BEGIN
      SET @mycount := 0;
      INSERT INTO results (data_id, mycounter)
          SELECT data.id, (@mycount := @mycount+1)
          FROM data LEFT JOIN results ON data.id = results.data_id
          WHERE ... GROUP BY ...
          HAVING ...
          ORDER BY ...;
  END //
  DELIMITER ;
  </code></pre>
  
  <p><strong>Analysis:</strong> This almost works as desired except I need <code>@mycount</code> to reset to zero each time it is run and the <code>SET</code> statement above is not achieving that. When debugging, I can see that the last value of <code>@mycount</code> is always remembered from the previous run of the stored procedure.  I've also tried setting it to zero after the insert.</p>
  
  <p><strong>Notes:</strong></p>
  
  <ol>
  <li>I am using a <code>@</code> variable to increment and keep track of row number. Perhaps there is another option here?</li>
  <li>The problem seems related to the Having clause.  When I run a similar but different query without the having clause, the variable resets no problem.</li>
  </ol>
  
  <p><strong>Question:</strong> Why can't I set <code>@mycount</code> to zero before running each time?  If it IS resetting and it's just that my Having clause causes an unexpected count, is there some better way to rewrite my SQL?</p>
  
  <p>I'm hoping someone has run into something similar, since this seems pretty obscure.</p>