MySQL CREATE FUNCTION statement seems to ignore WHERE condition
<p>I am writing the first MySQL stored function in my entire life, and I am stuck with something that seems silly even to me with no experience in this.</p>

<p>This is what I wrote until now:</p>



<pre><code>CREATE FUNCTION `credits_per_course`(COURSE_ID INT, USER_ID INT)
RETURNS int(11)
BEGIN

  DECLARE users_credits INT;

  DECLARE course_credits INT;
  DECLARE course_mandatory_credits INT;
  DECLARE course_total_credits INT;

  DECLARE lessons_count INT;

  DECLARE lessons CURSOR FOR
          SELECT * FROM t_lessons WHERE course_id = COURSE_ID;

  SELECT COUNT(*) INTO lessons_count FROM t_lessons WHERE course_id = COURSE_ID;

RETURN lessons_count;
END
</code></pre>

<p>I call this using <code>SELECT credits_per_course(1019, 262)</code>. The magic numbers are a course of which I know the lessons count and a test user, which at this point of the function is still ignored.</p>

<p>The course 1019 has four lessons. If I run the function, it gives me 2462 lessons, which are the count of ALL the lessons.</p>

<p>For test, I tried to:</p>

<ul>
<li>add a line <code>SET lessons_count = 42;</code> after the <code>SELECT COUNT(*)</code> query and it returns 42;</li>
<li>change the WHERE clause using a course id that doesn't exists like 99999 and, correctly, it returns 0 lessons;</li>
<li>change the WHERE clause explicitly passing a the 1019 course id but it still giving me 2462 lessons;</li>
<li>tried to run outside of the function the <code>SELECT COUNT(*)</code> query with course id of 1019 and it returns 4 lessons.</li>
</ul>

<p>I know that I am probably making some stupid mistake, but I can't figure out which one...</p>