UFN_SPONSORSHIPLOCATION_FULLSTRING

Returns a string representation of the specified sponsorship location including all parents of the location in the hierarchy.

Return

Return Type
nvarchar(max)

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DELIMITER	nvarchar(3)	IN
@INCLUDETYPES	bit	IN
@MAXLEVELS	tinyint	IN

Definition

Copy



create function dbo.UFN_SPONSORSHIPLOCATION_FULLSTRING(
    @ID uniqueidentifier,
    @DELIMITER nvarchar(3) = '',
    @INCLUDETYPES bit = 1,
    @MAXLEVELS tinyint = 0
)
returns nvarchar(max)
with execute as caller
as begin
    declare @RESULT nvarchar(max);
    declare @HIERARCHYLEVEL tinyint;

    with CTE(ID,HIERARCHYPATH,HIERARCHYLEVEL,LEVEL,NODESTRING) as
    (
    select
        SPONSORSHIPLOCATION.ID,
        SPONSORSHIPLOCATION.HIERARCHYPATH,
        SPONSORSHIPLOCATION.HIERARCHYPATH.GetLevel() HIERARCHYLEVEL,
        1 LEVEL,
        cast(SPONSORSHIPLOCATION.NAME + case @INCLUDETYPES when 1 then ' (' + SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION + ')' else '' end as nvarchar(max)) NODESTRING
    from
        dbo.SPONSORSHIPLOCATION
    inner join
        dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
    where
        SPONSORSHIPLOCATION.ID = @ID
    union all
    select
        CTE.ID,
        SPONSORSHIPLOCATION.HIERARCHYPATH,
        SPONSORSHIPLOCATION.HIERARCHYPATH.GetLevel() HIERARCHYLEVEL,
        CTE.LEVEL + 1 LEVEL,
        cast(SPONSORSHIPLOCATION.NAME + case @INCLUDETYPES when 1 then ' (' + SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION + ')' else '' end + @DELIMITER + CTE.NODESTRING as nvarchar(max)) NODESTRING
    from
        dbo.SPONSORSHIPLOCATION
    inner join
        dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
    inner join
        CTE on CTE.HIERARCHYPATH.GetAncestor(1) = SPONSORSHIPLOCATION.HIERARCHYPATH
    )
    select top 1 @RESULT = NODESTRING, @HIERARCHYLEVEL = HIERARCHYLEVEL
    from CTE
    where ID = @ID
    and (@MAXLEVELS = 0 or LEVEL <= @MAXLEVELS)
    order by LEVEL desc

    if @HIERARCHYLEVEL > 1
        set @RESULT = '... ' + @RESULT

    return @RESULT
end