CREATE FUNCTION [dbo].[GetProductName] (@AReferenceNumber INT, @AGuestNumber INT) RETURNS @Result TABLE (ProductName VARCHAR(50)) AS
BEGIN
  DECLARE @tmp TABLE (
    ProductNumber INT NULL,
    ProductNumber2 INT NULL,
    ProductNumber3 INT NULL,
    ProductNumber4 INT NULL
  )

  INSERT INTO @tmp
  SELECT 
    CASE WHEN @AGuestNumber = 0 THEN ReservationRoomLine.ProductNumber ELSE Reservations.ProductNumber END AS ProductNumber,
    CASE WHEN @AGuestNumber = 0 THEN ReservationRoomLine.ProductNumber2 ELSE Reservations.ProductNumber2 END AS ProductNumber2,
    CASE WHEN @AGuestNumber = 0 THEN ReservationRoomLine.ProductNumber3 ELSE Reservations.ProductNumber3 END AS ProductNumber3,
    CASE WHEN @AGuestNumber = 0 THEN ReservationRoomLine.ProductNumber4 ELSE Reservations.ProductNumber4 END AS ProductNumber4
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
  WHERE Reservations.ReferenceNumber = @AReferenceNumber
  AND Reservations.GuestNumber = @AGuestNumber

  INSERT INTO @Result
  SELECT DISTINCT Products.ProductName
  FROM
  (
   SELECT ProductNumber, ProductNumber2, ProductNumber3, ProductNumber4
   FROM @tmp
  ) p
  UNPIVOT
  (
    ProductNo for ProdName IN (ProductNumber, ProductNumber2, ProductNumber3, ProductNumber4)
  ) AS upvt
  INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = upvt.ProductNo
  WHERE ISNULL(ProductNo, 0) > 0

  RETURN
END