DELIMITER $$

DROP PROCEDURE IF EXISTS `sakila`.`customer_report` $$
CREATE PROCEDURE `customer_report`(in_customer_id INT,OUT out_total_hires INT, OUT out_active_hires INT)
BEGIN
   SELECT customer_id,first_name,last_name
     FROM customer
    WHERE customer_id=in_customer_id;

   SELECT r.rental_id,r.rental_date,r.return_date,f.title
     FROM customer c
     JOIN rental r USING (customer_id)
     JOIN inventory i USING(inventory_id)
     JOIN film f USING(film_id)
    WHERE customer_id=in_customer_id
      AND return_date IS NULL;

   SELECT SUM(IF(ISNULL(r.return_date),1,0)) AS sum_active,
          COUNT(*)
     INTO out_total_hires,out_active_hires
     FROM customer c
     JOIN rental r USING (customer_id)
     JOIN inventory i USING(inventory_id)
     JOIN film f USING(film_id)
    WHERE customer_id=in_customer_id;

END $$

DELIMITER ;