CREATE FUNCTION dbo.GetRoomNumberFromRefNo  (@ReferenceNumber INT, @GuestNumber INT )
RETURNS INT
AS
BEGIN
 DECLARE @Return INT
 DECLARE @RoomNumberId INT

 SELECT @RoomNumberId= RoomNumber FROM ReservationRoomLine WITH (nolock) WHERE LineNumber IN (SELECT TOP 1 LineNumber FROM ReservationRoomLineXtab WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber and GuestNumber=@GuestNumber)

 SELECT @return = Number FROM Rooms WITH (NOLOCK) WHERE RoomNumber = @RoomNumberId
 RETURN @return
END

