Question

Hi

can you convert this quiry into stored function called ( totalIncome ) using mySQL:

select hotelNo, sum(price) as 'Total Income'
from room
where roomNo in(select roomNo
               from booking as b , hotel as h
where deteFrom <=CURRENT_DATE()
and dateTo >= CURRENT_DATE()
and b.hotelNo=h.HotelNo
and hotelName='Marriot')
group by hotelNo;

Answer 1

I am assuming hotelName as varchar(30),

hotelNo as varchar(30) and sum(price) is DECIMAL(5,2)

You can change the types in method signature if you have different ones

DELIMITER $$

CREATE PROCEDURE totalIncome (
   IN hotelNme varchar (30),
OUT hotelNo varchar(30),
   OUT totalIncome decimal(5,2)
)
BEGIN
select hotelNo, sum(price) as 'totalIncome'
   from room
   where roomNo in(select roomNo
               from booking as b , hotel as h
   where deteFrom <=CURRENT_DATE()
   and dateTo >= CURRENT_DATE()
   and b.hotelNo=h.HotelNo
   and hotelName=hotelNme)
   group by hotelNo;

END