CREATE FUNCTION [dbo].[GetRoomTypeDifferentialStr] (@ArtdId INT, @Type INT) RETURNS VARCHAR(8000) AS
/*
  Created by Kent Johansen november 2013
  Room type differential update log 
  
  Changes:
  
*/

BEGIN
  DECLARE @Name VARCHAR(50)
  DECLARE @res VARCHAR(8000)
  SET @res = ''
  
  IF @Type = 0  -- rtdultRoomTypeDiff
  BEGIN
    DECLARE db_cur CURSOR FOR
      SELECT Name 
      FROM RoomTypeDifferential WITH (NOLOCK)
      INNER JOIN RoomTypeDifferentialUpdateLogXTab WITH (NOLOCK) ON RoomTypeDifferentialUpdateLogXTab.FK_ForeignId = RoomTypeDifferential.Id
        AND RoomTypeDifferentialUpdateLogXTab.FK_RTDifferentialUpdateLogId = @ArtdId
        AND RoomTypeDifferentialUpdateLogXTab.ForeignIdType = @Type
  END
  ELSE IF @Type = 1 -- rtdultRateLevelChannel
  BEGIN
    DECLARE db_cur CURSOR FOR
      SELECT Name
      FROM RateLevelChannel WITH (NOLOCK)
      INNER JOIN RoomTypeDifferentialUpdateLogXTab WITH (NOLOCK) ON RoomTypeDifferentialUpdateLogXTab.FK_ForeignId = RateLevelChannel.RateLevelChannelId
        AND RoomTypeDifferentialUpdateLogXTab.FK_RTDifferentialUpdateLogId = @ArtdId
        AND RoomTypeDifferentialUpdateLogXTab.ForeignIdType = @Type
  END
  ELSE IF @Type = 2 -- rtdultRateLevel
  BEGIN
    DECLARE db_cur CURSOR FOR
      SELECT Name 
      FROM RateLevel WITH (NOLOCK)
      INNER JOIN RoomTypeDifferentialUpdateLogXTab WITH (NOLOCK) ON RoomTypeDifferentialUpdateLogXTab.FK_ForeignId = RateLevel.RateLevelId
       AND RoomTypeDifferentialUpdateLogXTab.FK_RTDifferentialUpdateLogId = @ArtdId
       AND RoomTypeDifferentialUpdateLogXTab.ForeignIdType = @Type
  END
  ELSE IF @Type = 3 -- rtdultProducts
  BEGIN
    DECLARE db_cur CURSOR FOR
      SELECT ProductName 
      FROM Products WITH (NOLOCK)
      INNER JOIN RoomTypeDifferentialUpdateLogXTab WITH (NOLOCK) ON RoomTypeDifferentialUpdateLogXTab.FK_ForeignId = Products.ProductNumber
       AND RoomTypeDifferentialUpdateLogXTab.FK_RTDifferentialUpdateLogId = @ArtdId
       AND RoomTypeDifferentialUpdateLogXTab.ForeignIdType = @Type
  END
  
  OPEN db_cur
  FETCH NEXT FROM db_cur INTO @Name
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @res = @res + @Name + ', '
    FETCH NEXT FROM db_cur INTO @Name
  END
  CLOSE db_cur
  DEALLOCATE db_cur
  
  IF @res <> ''
    SET @res = SUBSTRING(@res, 1, LEN(@res)-1)
  
  RETURN @res
END