USP_DATALIST_CAMPAIGNHIERARCHY

This datalist returns a campaign hierarchy.

Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



                create procedure dbo.USP_DATALIST_CAMPAIGNHIERARCHY (
                    @CAMPAIGNID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @HIERARCHYPATH hierarchyid;
                    select @HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH from dbo.CAMPAIGN where CAMPAIGN.ID = @CAMPAIGNID;

                    select
                        CAMPAIGN.ID,
                        CAMPAIGN.NAME,
                        (select PARENT.ID from dbo.CAMPAIGN as PARENT where PARENT.HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH.GetAncestor(1)) PARENTCAMPAIGNID,
                        CAMPAIGNTYPECODE.DESCRIPTION,
                        CAMPAIGN.SEQUENCE
                    from
                        dbo.CAMPAIGN
                    left outer join
                        dbo.CAMPAIGNTYPECODE on CAMPAIGN.CAMPAIGNTYPECODEID = CAMPAIGNTYPECODE.ID
                    where
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                    order by
                        CAMPAIGN.HIERARCHYPATH;