USP_DATALIST_FAFNFGCAMPAIGNLEVELDISPLAYSITE

create procedure dbo.USP_DATALIST_FAFNFGCAMPAIGNLEVELDISPLAYSITE
(
  @CONTEXTID uniqueidentifier
)
as
    set nocount on;

  declare @NFGCAMPAIGNLEVELSITETABLE table (ID uniqueidentifier)
  insert into @NFGCAMPAIGNLEVELSITETABLE (ID) select FAFNFGCAMPAIGNLEVELDISPLAYSITE.SITEID from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE where FAFNFGCAMPAIGNLEVELDISPLAYSITE.NFGCAMPAIGNLEVELID = @CONTEXTID

    select S.ID,
        (select PARENTSITE.ID from dbo.SITE as PARENTSITE where PARENTSITE.HIERARCHYPATH = S.HIERARCHYPATH.GetAncestor(1) and PARENTSITE.ID in ( select ID from @NFGCAMPAIGNLEVELSITETABLE )) as PARENTID,    
            S.NAME,
        row_number() over (partition by S.HIERARCHYPATH.GetAncestor(1) order by S.HIERARCHYPATH) as SEQUENCE  
    from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE FNFGS (nolock)
  join dbo.SITE S (nolock) on FNFGS.SITEID = S.ID
    where FNFGS.NFGCAMPAIGNLEVELID = @CONTEXTID
  order by S.HIERARCHYPATH