CREATE FUNCTION dbo.GetProductNumberFromReservation 
(
	@ReferenceNumber INT, @GuestNumber INT
)
RETURNS INT
AS
BEGIN
	DECLARE @Result INT
	DECLARE @LineNumber INT

	SET @Result = 0
	SET @LineNumber = 0

	SELECT TOP 1 @LineNumber = LineNumber FROM ReservationRoomLineXTAB (NOLOCK) 
	WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber
	ORDER BY LineNumber

	SET @LineNumber = ISNULL(@LineNumber, 0)

	IF @LineNumber = 0
		IF @GuestNumber = 0
		BEGIN
		   SELECT TOP 1 @LineNumber = LineNumber FROM ReservationRoomLineXTAB (NOLOCK) 
		   WHERE ReferenceNumber = @ReferenceNumber
		   ORDER BY GuestNumber

		   SET @LineNumber = ISNULL(@LineNumber, 0)
		END

	IF @LineNumber > 0
		SELECT @Result = ProductNumber From ReservationRoomLine (NOLOCK)
		WHERE LineNumber = @LineNumber
	

	RETURN @Result

END
GO

