UFN_EVENTLOCATION_ASSOCIATEDCATEGORYCODES

create function dbo.UFN_EVENTLOCATION_ASSOCIATEDCATEGORYCODES
(
    @EVENTLOCATIONID uniqueidentifier = null
)
returns xml
with execute as caller
as begin

declare @TEMP table (CATEGORYCODEID uniqueidentifier);
if (@EVENTLOCATIONID is null)
    begin
        insert into @TEMP (CATEGORYCODEID)
        (
            select
                CODES.CATEGORYCODE
            from
            (
                select
                    E.EVENTCATEGORYCODEID as CATEGORYCODE
                from
                    dbo.EVENT E
                where
                    E.EVENTLOCATIONID is null and
                    E.EVENTCATEGORYCODEID is not null

                union all

                select
                    P.PROGRAMCATEGORYCODEID as CATEGORYCODE
                from
                    dbo.PROGRAM P
                inner join
                    dbo.EVENT E on E.PROGRAMID = P.ID
                left join
                    dbo.PROGRAMEVENTLOCATION PL on PL.EVENTID = E.ID
                where
                    PL.EVENTLOCATIONID is null and
                    P.PROGRAMCATEGORYCODEID is not null
            ) as CODES
        );

        if exists(select 1 from dbo.EVENT E where E.EVENTLOCATIONID is null and E.EVENTCATEGORYCODEID is null) 
        or exists(select 1 from dbo.PROGRAM P inner join dbo.EVENT E on E.PROGRAMID = P.ID inner join dbo.PROGRAMEVENTLOCATION PL on PL.EVENTID = E.ID where P.PROGRAMCATEGORYCODEID is null and PL.EVENTLOCATIONID is null)
        begin
            insert into @TEMP(CATEGORYCODEID)
            select '00000000-0000-0000-0000-000000000000'
        end
    end
else
    begin
        insert into @TEMP (CATEGORYCODEID)
        (
            select
                CODES.CATEGORYCODE
            from
            (
                select
                    E.EVENTCATEGORYCODEID as CATEGORYCODE
                from
                    dbo.EVENT E
                where
                    E.EVENTLOCATIONID = @EVENTLOCATIONID and
                    E.EVENTCATEGORYCODEID is not null

                union all

                select
                    P.PROGRAMCATEGORYCODEID as CATEGORYCODE
                from
                    dbo.PROGRAM P
                inner join
                    dbo.EVENT E on E.PROGRAMID = P.ID
                inner join
                    dbo.PROGRAMEVENTLOCATION PL on PL.EVENTID = E.ID
                where
                    PL.EVENTLOCATIONID = @EVENTLOCATIONID and
                    P.PROGRAMCATEGORYCODEID is not null
            ) as CODES
        )

        if exists(select 1 from dbo.EVENT E where E.EVENTLOCATIONID = @EVENTLOCATIONID and E.EVENTCATEGORYCODEID is null) 
        or exists(select 1 from dbo.PROGRAM P inner join dbo.EVENT E on E.PROGRAMID = P.ID inner join dbo.PROGRAMEVENTLOCATION PL on PL.EVENTID = E.ID where P.PROGRAMCATEGORYCODEID is null and PL.EVENTLOCATIONID = @EVENTLOCATIONID)
        begin
            insert into @TEMP(CATEGORYCODEID)
            select '00000000-0000-0000-0000-000000000000'
        end
    end

return
    (
        select distinct
            CODES.CATEGORYCODEID
        from
            @TEMP CODES
        for xml raw('ITEM'),type,elements,root('CATEGORYCODES'),BINARY BASE64
    )
end