USP_DATALIST_FAFEVENTAUTOMATCHSITES

create procedure dbo.USP_DATALIST_FAFEVENTAUTOMATCHSITES
(
  @EVENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as
    set nocount on;

    select S.ID,
           S.NAME,
           S.DESCRIPTION,
       (select PARENTSITE.ID from dbo.SITE as PARENTSITE where PARENTSITE.HIERARCHYPATH = S.HIERARCHYPATH.GetAncestor(1)) as PARENTID    
    from dbo.EVENTAUTOMATCHSITE ES (nolock)
  join dbo.SITE S (nolock) on ES.SITEID = S.ID
    where EVENTID = @EVENTID
  order by S.HIERARCHYPATH