USP_DATALIST_FAFNFGCAMPAIGNDISPLAYSITE

CREATE procedure dbo.USP_DATALIST_FAFNFGCAMPAIGNDISPLAYSITE
(
  @CONTEXTID uniqueidentifier
)
as
    set nocount on;

  declare @NFGCAMPAIGNSITETABLE table (ID uniqueidentifier)
  insert into @NFGCAMPAIGNSITETABLE (ID) select FAFNFGCAMPAIGNDISPLAYSITE.SITEID from dbo.FAFNFGCAMPAIGNDISPLAYSITE where FAFNFGCAMPAIGNDISPLAYSITE.NFGCAMPAIGNID = @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 @NFGCAMPAIGNSITETABLE )) as PARENTID,    
            S.NAME,
        row_number() over (partition by S.HIERARCHYPATH.GetAncestor(1) order by S.HIERARCHYPATH) as SEQUENCE  
    from dbo.FAFNFGCAMPAIGNDISPLAYSITE FNFGS (nolock)
  join dbo.SITE S (nolock) on FNFGS.SITEID = S.ID
    where FNFGS.NFGCAMPAIGNID = @CONTEXTID
  order by S.HIERARCHYPATH