CREATE FUNCTION [dbo].[GetRoomNameFromRefNo]  (@ReferenceNumber INT, @GuestNumber INT )
RETURNS VARCHAR(50)
AS
BEGIN
 DECLARE @Return VARCHAR(50)
 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 = Name FROM Rooms WITH (NOLOCK) WHERE RoomNumber = @RoomNumberId
 RETURN @return
END