USP_DATALIST_FAFNFGAllDefaultSites

CREATE procedure dbo.USP_DATALIST_FAFNFGAllDefaultSites
as
    set nocount on;

  Select S.ID,
       S.NAME,
       row_number() over (partition by S.HIERARCHYPATH.GetAncestor(1) order by S.HIERARCHYPATH) as SEQUENCE,
       (select PARENTSITE.ID from dbo.SITE as PARENTSITE where PARENTSITE.HIERARCHYPATH = S.HIERARCHYPATH.GetAncestor(1)) as PARENTID
  from dbo.SITE S (nolock) 
      order by S.HIERARCHYPATH