UFN_MKTMARKETINGPLANITEM_PATH_BULK

create function dbo.[UFN_MKTMARKETINGPLANITEM_PATH_BULK]
(
  @MARKETINGPLANITEMID uniqueidentifier,
  @RETURNFULLPATH bit
)
returns table
as
return (
  with [PLANPATH_CTE1] as (
    select
      [ID],
      [PARENTMARKETINGPLANITEMID],
      isnull([NAME], '') as [PATH]
    from 
      dbo.[MKTMARKETINGPLANITEM]
    where (
        (@RETURNFULLPATH = 0 and [MKTMARKETINGPLANITEM].[ID] = (select [PARENTMARKETINGPLANITEMID] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID))
        or
        (@RETURNFULLPATH = 1 and [MKTMARKETINGPLANITEM].[ID] = @MARKETINGPLANITEMID)
    )
  ),
  [PLANPATH_CTE2] as (
    select
      [PLANPATH_CTE1].[ID],
      [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID],
      isnull([MKTMARKETINGPLANITEM].[NAME] + ' \ ', '') + [PLANPATH_CTE1].[PATH] as [PATH]
    from
      [PLANPATH_CTE1]
      left join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [PLANPATH_CTE1].[PARENTMARKETINGPLANITEMID]
  ),
  [PLANPATH_CTE3] as (
    select
      [PLANPATH_CTE2].[ID],
      [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID],
      isnull([MKTMARKETINGPLANITEM].[NAME] + ' \ ', '') + [PLANPATH_CTE2].[PATH] as [PATH]
    from
      [PLANPATH_CTE2]
      left join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [PLANPATH_CTE2].[PARENTMARKETINGPLANITEMID]
  ),
  [PLANPATH_CTE4] as (
    select
      [PLANPATH_CTE3].[ID],
      [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID],
      isnull([MKTMARKETINGPLANITEM].[NAME] + ' \ ', '') + [PLANPATH_CTE3].[PATH] as [PATH]
    from
      [PLANPATH_CTE3]
      left join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [PLANPATH_CTE3].[PARENTMARKETINGPLANITEMID]
  )
    select
      isnull([MKTMARKETINGPLANITEM].[NAME] + ' \ ', '') + [PLANPATH_CTE4].[PATH] as [PATH]
    from
      [PLANPATH_CTE4]
      left join dbo.[MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[ID] = [PLANPATH_CTE4].[PARENTMARKETINGPLANITEMID]
);