CREATE FUNCTION dbo.GetReservationHotelNumber (@ReferenceNumber INT, @GuestNumber INT, @HotelNumber INT = -1)
RETURNS INT AS
/*
  Created by Jacob Pedersen 2007-10-19

  Description: Returns HotelNumber of a Reservation. Incase of a mainreservation which covers multiple hotels, it's possible to ask if it covers a certain hotel and this hotelnumber will be returned. NB! Only one hotel can be returned

  Changes:
  N/A
*/
BEGIN 
  DECLARE @Result INT

  IF @GuestNumber = 0
  BEGIN
    SET @HotelNumber = 0

    DECLARE #Cursor CURSOR LOCAL FAST_FORWARD FOR

      SELECT DISTINCT CASE ISNULL(RL.RoomNumber, 0) WHEN 0 THEN RT.HotelNumber ELSE Rooms.HotelNumber END FROM dbo.ReservationRoomLine RL WITH (NOLOCK) 
      INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber=@ReferenceNumber --AND RLX.GuestNumber=@GuestNumber
      LEFT OUTER JOIN dbo.Rooms WITH (NOLOCK) ON RL.RoomNumber=Rooms.RoomNumber
      LEFT OUTER JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RL.BookedTypeNumber=RT.RoomTypeNumber
      UNION
      SELECT DISTINCT CASE ISNULL(RL.ConferenceRoomNumber, 0) WHEN 0 THEN RT.HotelNumber ELSE CR.HotelNumber END FROM dbo.ReservationConferenceLine RL WITH (NOLOCK) 
      LEFT OUTER JOIN dbo.ConferenceRooms CR WITH (NOLOCK) ON RL.ConferenceRoomNumber=CR.ConferenceRoomNumber
      LEFT OUTER JOIN dbo.ConferenceRoomTypes RT WITH (NOLOCK) ON RL.BookedConferenceRoomTypeNumber=RT.ConferenceRoomTypeNumber
      WHERE ReferenceNumber=@ReferenceNumber --AND GuestNo=@GuestNumber

    OPEN #Cursor
    FETCH NEXT FROM #Cursor INTO @Result

    IF (@HotelNumber > -1) OR (@Result IS NULL) -- if -1 then just return the first found
      WHILE (@@FETCH_STATUS <> -1) AND (@Result <> @HotelNumber)
      BEGIN
        FETCH NEXT FROM #Cursor INTO @Result
        IF (@HotelNumber = -1) AND (NOT @Result IS NULL)
          BREAK
      END

    CLOSE #Cursor
    DEALLOCATE #Cursor
  END
  ELSE
  BEGIN
    -- Assume a roomline
    SELECT @Result = CASE ISNULL(RL.RoomNumber, 0) WHEN 0 THEN RT.HotelNumber ELSE Rooms.HotelNumber END FROM dbo.ReservationRoomLine RL WITH (NOLOCK) 
    INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber AND RLX.ReferenceNumber=@ReferenceNumber AND RLX.GuestNumber=@GuestNumber
    LEFT OUTER JOIN dbo.Rooms WITH (NOLOCK) ON RL.RoomNumber=Rooms.RoomNumber
    LEFT OUTER JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RL.BookedTypeNumber=RT.RoomTypeNumber
    IF @@rowcount = 0 -- possibly a hall subres
    BEGIN
      SELECT @Result = CASE ISNULL(RL.ConferenceRoomNumber, 0) WHEN 0 THEN RT.HotelNumber ELSE CR.HotelNumber END FROM dbo.ReservationConferenceLine RL WITH (NOLOCK) 
      LEFT OUTER JOIN dbo.ConferenceRooms CR WITH (NOLOCK) ON RL.ConferenceRoomNumber=CR.ConferenceRoomNumber
      LEFT OUTER JOIN dbo.ConferenceRoomTypes RT WITH (NOLOCK) ON RL.BookedConferenceRoomTypeNumber=RT.ConferenceRoomTypeNumber
      WHERE ReferenceNumber=@ReferenceNumber AND GuestNo=@GuestNumber
    END
  END

  -- If no HotelNumber was found then return our input instead of null
  IF @Result IS NULL
    SET @Result = @HotelNumber

  RETURN @Result
END
