MySQL - using stored procedure with nested statements -
newbie mysql here - i'm having trouble feeding parameters nested statements in stored procedure.
the following 3 statements work when executed sequentially.
insert 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 rental.movie_id = 3014 , rental.format = "dvd" , membership_id = 10004; update rental set rental_cost_due = rental_cost rental.movie_id = 3014 , rental.format = "dvd" , membership_id = 10004;
however, when try , use stored procedure 1 set of parameters clauses give error messages such error 1054 (42s22): unknown column 'movie_id_in' in 'where clause'
delimiter // create procedure usp_rent_movie (in movie_id_in int, membership_id_in int, format_in varchar(15), no_nights_in int) insert 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 rental.movie_id = movie_id_in , rental.format = format_in , membership_id = membership_id_in; update rental set rental_cost_due = rental_cost rental.movie_id = movie_id_in , rental.format = format_in , membership_id = membership_id_in; end// delimiter ;
apologies if simple error, i've been @ hours!
you need use begin
, end
if have multiple statements in procedure body.
delimiter // create procedure usp_rent_movie (in movie_id_in int, membership_id_in int, format_in varchar(15), no_nights_in int) begin insert 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 rental.movie_id = movie_id_in , rental.format = format_in , membership_id = membership_id_in; update rental set rental_cost_due = rental_cost rental.movie_id = movie_id_in , rental.format = format_in , membership_id = membership_id_in; end// delimiter ;