UFN_EVENTLOCATION_ASSOCIATEDSITES

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

declare @TEMP table (SITEID uniqueidentifier);
if (@EVENTLOCATIONID is null)
begin
    insert into @TEMP (SITEID)
    (
        select
            EVENTSITE.SITEID
        from
            dbo.EVENTSITE
        inner join
            dbo.EVENT on EVENT.ID = EVENTSITE.EVENTID
        where
            EVENT.EVENTLOCATIONID is null
    )
end
else
begin
    insert into @TEMP (SITEID)
    (
        select
            EVENTSITE.SITEID as SITEID
        from
            dbo.EVENTSITE
        inner join
            dbo.EVENT on EVENT.ID = EVENTSITE.EVENTID
        where
            EVENT.EVENTLOCATIONID = @EVENTLOCATIONID        
    )
end
return
    (
        select distinct
            SITES.SITEID
        from
            @TEMP SITES
        for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64
    )

end