USP_NFGLEVEL_REMOVECHILDLEVELSITES

create procedure dbo.USP_NFGLEVEL_REMOVECHILDLEVELSITES
(
  @NFGCAMPAIGNLEVELID uniqueidentifier,  
  @XML xml
)
as 

 SET NOCOUNT ON;


  DECLARE @HIERARCHYPATH hierarchyid
  DECLARE @TempTbl table ([SITEID] uniqueidentifier)
  DECLARE @ChildLevelIDTable table ([ID] uniqueidentifier)


  insert into @TempTbl select  [SITEID]  from dbo.UFN_FAFPROGRAM_GETSITES_FROMITEMLISTXML(@XML)

    select @HIERARCHYPATH = HIERARCHYPATH from dbo.FAFNFGCAMPAIGNLEVEL Where ID = @NFGCAMPAIGNLEVELID

  insert into @ChildLevelIDTable select  [ID]  from  dbo.FAFNFGCAMPAIGNLEVEL Where HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH)=1 and ID <> @NFGCAMPAIGNLEVELID
 begin try

  delete from FAFNFGCAMPAIGNLEVELDISPLAYSITE  where NFGCAMPAIGNLEVELID in (select ID from @ChildLevelIDTable) and SITEID not in ( select SITEID from @TempTbl)

end try
begin catch
      exec dbo.USP_RAISE_ERROR;
      ROLLBACK TRAN
      return 1;
    end catch
    return 0;