USP_DATALIST_FAFNFGCAMPAIGNLEVELALLCHILDDISPLAYSITE

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

  declare @HIERARCHYPATH hierarchyid
  declare @NFGCAMPAIGNLEVELCHILDTABLE table (ID uniqueidentifier)

  set @HIERARCHYPATH = (Select HIERARCHYPATH From dbo.FAFNFGCAMPAIGNLEVEL Where ID = @CONTEXTID)

  insert into @NFGCAMPAIGNLEVELCHILDTABLE (ID) select ID from dbo.FAFNFGCAMPAIGNLEVEL Where HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH)=1 and ID <> @CONTEXTID

    select S.ID,
         S.NAME 
    from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE FNFGS (nolock)
  join dbo.SITE S (nolock) on FNFGS.SITEID = S.ID
    where FNFGS.NFGCAMPAIGNLEVELID in (select ID from @NFGCAMPAIGNLEVELCHILDTABLE)