UFN_SMARTQUERY_MULTILEVELEVENTREGISTRANT

create function dbo.UFN_SMARTQUERY_MULTILEVELEVENTREGISTRANT
(
    @IDSETREGISTERID uniqueidentifier = null,
    @EVENTID uniqueidentifier = null,
    @MAXROWS int = 500
)
returns @T table 
(
    ID uniqueidentifier not null, 
    CONSTITUENTID uniqueidentifier,
    EVENTID uniqueidentifier,
    REGISTRANTNAME nvarchar(700),
    EVENTNAME nvarchar(100)
)
as
begin

    declare @EVENTHIERARCHYID hierarchyid;

    select @EVENTHIERARCHYID = HIERARCHYPATH
        from dbo.EVENTHIERARCHY
        where ID = @EVENTID;

    with REGISTRANT_CTE as (
        select
            REGISTRANT.ID,
            REGISTRANT.CONSTITUENTID,
            REGISTRANT.EVENTID,
            row_number() over(partition by REGISTRANT.CONSTITUENTID order by REGISTRANT.ID) as OCCURANCERANK
        from dbo.REGISTRANT
        inner join dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = REGISTRANT.EVENTID and EVENTHIERARCHY.HIERARCHYPATH.IsDescendantOf(@EVENTHIERARCHYID) = 1
        left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) as IDSETFILTER on IDSETFILTER.ID = REGISTRANT.ID
        where
            REGISTRANT.CONSTITUENTID is not null
            and (@IDSETREGISTERID is null or IDSETFILTER.ID is not null)
    )
    insert into @T (ID, CONSTITUENTID, EVENTID, REGISTRANTNAME, EVENTNAME)
    select
        REGISTRANT_CTE.ID,
        REGISTRANT_CTE.CONSTITUENTID,
        REGISTRANT_CTE.EVENTID,
        dbo.UFN_REGISTRANT_GETNAME(REGISTRANT_CTE.ID),
        EVENT.NAME
    from REGISTRANT_CTE
    inner join dbo.EVENT on EVENT.ID = REGISTRANT_CTE.EVENTID
    where REGISTRANT_CTE.OCCURANCERANK = 1;

    return;
end;