USP_DATAFORMTEMPLATE_EDIT_CAMPAIGNHIERARCHY

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CAMPAIGNHIERARCHY (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier,
                        @CAMPAIGNHIERARCHY xml
                    ) as begin
                        set nocount on;

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

                        begin try                
                            declare @TempTbl table (
                                ID uniqueidentifier,
                                PARENTCAMPAIGNID uniqueidentifier,
                                NAME nvarchar(100),
                                SEQUENCE int
                            );

                            insert into 
                                @TempTbl
                            select
                                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                                T.c.value('(PARENTCAMPAIGNID)[1]','uniqueidentifier') AS 'PARENTCAMPAIGNID',
                                T.c.value('(NAME)[1]','nvarchar(100)') AS 'NAME',
                                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE'
                            from
                                @CAMPAIGNHIERARCHY.nodes('/CAMPAIGNHIERARCHY/ITEM') T(c);                                

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

                            -- validate that there's only one root in the structure

                            if ((select count(ID) from @TempTbl where PARENTCAMPAIGNID is null) > 1)
                                raiserror ('ERR_CAMPAIGNHIERARCHY_MULTIPLEROOT',13,1);

                            -- validate that any campaigns in the hierarchy with pre-existing paths originally belonged to this hierarchy

                            -- (i.e. don't yank campaigns out of other hierarchies)

                            if exists (
                                select
                                    CAMPAIGN.ID
                                from
                                    dbo.CAMPAIGN
                                inner join
                                    @TempTbl as [temp] on CAMPAIGN.ID = [temp].ID
                                where
                                    (
                                        CAMPAIGN.HIERARCHYPATH.GetAncestor(1) <> hierarchyid::GetRoot() 
                                    and
                                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@OLDHIERARCHYROOTPATH) = 0
                                )
                            )
                                raiserror ('ERR_CAMPAIGNHIERARCHY_CAMPAIGNALREADYINHIERARCHY',13,1);

                            -- Reroot campaigns that have been removed from the hierarchy

                            -- (Using a cursor because the insert position UFN would return the same value for all calls in a single update statement)

                            declare CAMPAIGNCURSOR cursor local fast_forward for
                                select ID from dbo.CAMPAIGN where CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@OLDHIERARCHYROOTPATH) = 1 except select ID from @TempTbl;

                            declare @CAMPAIGNTOREMOVEID uniqueidentifier;
                            open CAMPAIGNCURSOR;
                            fetch next from CAMPAIGNCURSOR into @CAMPAIGNTOREMOVEID;

                            while @@FETCH_STATUS = 0
                            begin
                                update 
                                    dbo.CAMPAIGN 
                                set 
                                    CAMPAIGN.HIERARCHYPATH = dbo.UFN_CAMPAIGN_GETHIERARCHYPATHINSERTPOSITION(null),
                                    DATECHANGED = @CURRENTDATE,
                                    CHANGEDBYID = @CHANGEAGENTID
                                where
                                    CAMPAIGN.ID = @CAMPAIGNTOREMOVEID;

                                fetch next from CAMPAIGNCURSOR into @CAMPAIGNTOREMOVEID;
                            end
                            --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                            close CAMPAIGNCURSOR;
                            deallocate CAMPAIGNCURSOR;

                            -- Update the hierarchyids using using a recursive cte to build up paths (i.e. '/2/1/3')

                            with CAMPAIGN_ROWNUMBER_CTE as (
                                select
                                    ID,
                                    PARENTCAMPAIGNID,
                                    row_number() over (partition by PARENTCAMPAIGNID order by SEQUENCE) as ROWNUM,
                                    SEQUENCE
                                from
                                    @TempTbl
                            ),
                            CAMPAIGN_PATHS_CTE as (
                                select
                                    ID,
                                    PARENTCAMPAIGNID,
                                    cast(dbo.UFN_CAMPAIGN_GETHIERARCHYPATHINSERTPOSITION(null) as varchar(900)) as HIERARCHYPATH,
                                    SEQUENCE
                                from
                                    @TempTbl
                                where
                                    PARENTCAMPAIGNID is null

                                union all

                                select
                                    CAMPAIGN_ROWNUMBER_CTE.ID,
                                    CAMPAIGN_ROWNUMBER_CTE.PARENTCAMPAIGNID,
                                    cast(HIERARCHYPATH + cast(CAMPAIGN_ROWNUMBER_CTE.ROWNUM as varchar(20)) + '/' as varchar(900)),
                                    CAMPAIGN_ROWNUMBER_CTE.SEQUENCE
                                from
                                    CAMPAIGN_PATHS_CTE
                                inner join
                                    CAMPAIGN_ROWNUMBER_CTE on CAMPAIGN_ROWNUMBER_CTE.PARENTCAMPAIGNID = CAMPAIGN_PATHS_CTE.ID
                            )
                            update
                                dbo.CAMPAIGN
                            set
                                CAMPAIGN.HIERARCHYPATH = cast(CAMPAIGN_PATHS_CTE.HIERARCHYPATH as hierarchyid),
                                CAMPAIGN.SEQUENCE = CAMPAIGN_PATHS_CTE.SEQUENCE,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID                
                            from
                                dbo.CAMPAIGN
                            inner join
                                CAMPAIGN_PATHS_CTE on CAMPAIGN.ID = CAMPAIGN_PATHS_CTE.ID;                                

                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;

                    end