title: MySQL - using stored procedure with nested statements
body: <p>Newbie to MySQL here - I'm having trouble feeding parameters into nested statements in a stored procedure.</p>
  
  <p>The following 3 statements work perfectly when executed sequentially.</p>
  
  <pre><code>insert into rental(movie_id, membership_id, format, no_nights) 
  values (3014, 10004, "dvd", 2);
  
  update rental  
  inner join movie_format on rental.movie_id = movie_format.movie_id  
  set rental_cost = daily_rental_cost * no_nights  
  where rental.movie_id = 3014 and rental.format = "dvd" and membership_id = 10004;
  
  update rental  
  set rental_cost_due = rental_cost  
  where rental.movie_id = 3014 and rental.format = "dvd" and membership_id = 10004;      
  </code></pre>
  
  <p>However, when I try and use a stored procedure with one set of parameters the where clauses give error messages such as ERROR 1054 (42S22): Unknown column 'movie_id_in' in 'where clause'</p>
  
  <pre><code>delimiter //  
  create procedure usp_rent_movie   
          (IN movie_id_in int, membership_id_in int, format_in varchar(15), no_nights_in int)  
  
      insert into rental(movie_id, membership_id, format, no_nights) 
      values (movie_id_in, membership_id_in, format_in, no_nights_in);  
  
      update rental  
      inner join movie_format on rental.movie_id = movie_format.movie_id  
      set rental_cost = daily_rental_cost * no_nights   
      where rental.movie_id = movie_id_in and rental.format = format_in and membership_id = membership_id_in;  
  
      update rental   
      set rental_cost_due = rental_cost  
      where rental.movie_id = movie_id_in and rental.format = format_in and membership_id = membership_id_in;   
  end//   
  delimiter ;
  </code></pre>
  
  <p>Apologies if this is a simple error, I've been at this for hours!</p>