CREATE FUNCTION dbo.GetInUseNote (@ObjectType TINYINT, @ObjectNumber INT, @NoteType TINYINT, @StartDate DATETIME, @EndDate DATETIME)
--RETURNS VARCHAR(8000) AS  
RETURNS VARCHAR(255) AS  
/*
    Created By: Jacob Pedersen 20040108

    Last Updated:	

    ObjectTypes:
    0 : Room
    1 : Hall
*/
BEGIN 
  DECLARE
  @Note VARCHAR(8000),
  @tmpNote VARCHAR(8000),
  @ReferenceNumber INT

  SET @tmpNote = ''
  
  IF @NoteType = 0
	SET @ReferenceNumber = 0
  ELSE
	SET @ReferenceNumber = -2	

  DECLARE #NoteCursor CURSOR FAST_FORWARD READ_ONLY FOR
  
  SELECT Note 
  FROM InUseNote (NOLOCK)     
  INNER JOIN InUseNoteXTab NX (NOLOCK) ON NX.NoteNumber=InUseNote.NoteNumber 
    AND ObjectType=@ObjectType
  INNER JOIN ReservationRoomLine (NOLOCK) ON ReservationRoomLine.LineNumber = NX.ObjectNumber
    AND ArrivalDate + ' ' + ArrivalTime < @EndDate 
    AND DepartureDate + ' '+ DepartureTime > @StartDate
    AND RoomNumber = @ObjectNumber
  INNER JOIN REservationRoomlineXTAB ON ReservationRoomLine.LineNumber = RESERVATIONROOMLINEXTAB.LineNumber
	AND ReferenceNUmber = @ReferenceNumber

  OPEN #NoteCursor
  FETCH NEXT FROM #NoteCursor
  INTO @Note

  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    IF @tmpNote <> ''
      SET @tmpNote = @tmpNote + '--------------------------------------------------' +char(13)

    SET @tmpNote = @tmpNote + @Note +char(13)

    FETCH NEXT FROM #NoteCursor
    INTO @Note
  END

  CLOSE #NoteCursor
  DEALLOCATE #NoteCursor

  RETURN @tmpNote
END

