UFN_EVENT_GETALLEVENTSINHIERARCHY

Gets basic info about all events in an event hierarchy, based on the main event ID

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@MAINEVENTID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_EVENT_GETALLEVENTSINHIERARCHY(@MAINEVENTID uniqueidentifier)
returns table
as return(
    select
        EVENT.ID, EVENT.NAME, CHILD.HIERARCHYPATH.GetLevel() LEVEL
    from
        dbo.EVENTHIERARCHY CHILD
    inner join
        dbo.EVENTHIERARCHY MAINEVENT on CHILD.HIERARCHYPATH.IsDescendantOf(MAINEVENT.HIERARCHYPATH) = 1
    inner join
        dbo.EVENT on EVENT.ID = CHILD.ID
    where
        MAINEVENT.ID = @MAINEVENTID

    union

    select 
        ID, NAME, 1 as LEVEL
    from 
        dbo.EVENT
    where
        ID = @MAINEVENTID
)