UFN_REVENUE_SITE_GETNAMEHIERARCHYFROMREVENUEID

Gets the string representing the site hierarchy from a site ID

Return

Return Type
nvarchar(max)

Parameters

Parameter	Parameter Type	Mode	Description
@REVENUEID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_REVENUE_SITE_GETNAMEHIERARCHYFROMREVENUEID(
  @REVENUEID uniqueidentifier
)
returns nvarchar(max)
as begin

    declare @DESIGNATIONLEVEL uniqueidentifier
  set @DESIGNATIONLEVEL = [dbo].[UFN_DESIGNATIONLEVEL_GETIDFROMREVENUEID](@REVENUEID)

    if (@DESIGNATIONLEVEL is null)
         return ''

    declare @SITEID uniqueidentifier

    select
     @SITEID = [DESIGNATIONLEVEL].[SITEID]
    from dbo.[DESIGNATIONLEVEL]
    where
    [DESIGNATIONLEVEL].[ID] = @DESIGNATIONLEVEL

    if (@SITEID is null)
        return ''

    declare @ACCUM nvarchar(max) = ''
    declare @CHILD  hierarchyid
  declare @PARENT hierarchyid

  --Base case

  select
   @ACCUM = [SITE].[NAME],
   @CHILD = [SITE].[HIERARCHYPATH]
   from dbo.[SITE]
   where [SITE].ID = @SITEID

   -- Get Parents

   while(@CHILD.GetAncestor(1) is not NULL)
   begin
    select
      @ACCUM = [SITE].[NAME]+' / '+@ACCUM,
      @PARENT = [SITE].[HIERARCHYPATH]
     from dbo.[SITE]
     where [SITE].[HIERARCHYPATH] = @CHILD.GetAncestor(1)

     set @CHILD = @PARENT
     set @PARENT = null
     continue
    end

    return @ACCUM
  end