CREATE FUNCTION dbo.GetReservationExternalReference
(
	@RefenceNumber INT,
	@GuestNumber INT,
	@Qualifier VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
	
	DECLARE @Result VARCHAR(255);

	
	WITH ExtRef(ID, ParentID, Value, Qualifier) AS
	(
		SELECT ID, ParentID, VALUE, Qualifier
		FROM ExternalReference E
		INNER JOIN ReservationExternalRefXtab re (nolock) ON re.FK_ExternalRefID = e.ID 
		AND FK_ReferenceNumber = @RefenceNumber AND FK_GuestNumber = @GuestNumber
		UNION ALL
		Select E2.ID, E2.ParentID, E2.Value, E2.Qualifier
		FROM ExternalReference E2
		INNER JOIN ExtRef ER On E2.ParentID = ER.ID
	)
	SELECT TOP 1 @Result = Value
	FROM ExtRef
	WHERE Qualifier = @Qualifier
	ORDER BY ID

	SET @Result = ISNULL(@result, '')
	
	RETURN @Result

END
GO

