CREATE FUNCTION dbo.GetRoomFromReservation (@ReferenceNumber INT, @GuestNumber INT )
RETURNS INT
AS
BEGIN
 DECLARE @Return INT
 SELECT Top 1 @Return = case Reservations.guestnumber when 0 then Rooms.Number else
  case Reservations.sidehouse when 1 then Rooms.SideHouseNumber else Rooms.Number end end
  FROM RESERVATIONS WITH (NOLOCK)
    LEFT OUTER JOIN RESERVATIONROOMLINEXTAB WITH (nolock) ON RESERVATIONS.ReferenceNumber = RESERVATIONROOMLINEXTAB.ReferenceNumber
      AND RESERVATIONS.GuestNumber = RESERVATIONROOMLINEXTAB.GuestNumber
    LEFT OUTER JOIN RESERVATIONROOMLINE WITH (nolock) ON RESERVATIONROOMLINEXTAB.LineNumber = RESERVATIONROOMLINE.LINENUMBER
    LEFT OUTER JOIN ROOMS WITH (nolock) ON ReservationRoomLine.RoomNumber = Rooms.RoomNumber
  WHERE Reservations.ReferenceNumber= @ReferenceNumber AND Reservations.GuestNumber= @GuestNumber
  ORDER BY Rooms.RoomNumber desc

 RETURN @return
END

