USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPLOCATIONHIERARCHY

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPLOCATIONHIERARCHY
                    (
                        @CHANGEAGENTID uniqueidentifier,
                        @SPONSORSHIPLOCATIONHIERARCHY 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,
                                PARENTLOCATIONID uniqueidentifier,
                                SEQUENCE int);

                            insert into 
                                @TempTbl
                            select
                                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                                T.c.value('(PARENTLOCATIONID)[1]','uniqueidentifier') AS 'PARENTLOCATIONID',
                                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE'
                            from
                                @SPONSORSHIPLOCATIONHIERARCHY.nodes('/SPONSORSHIPLOCATIONHIERARCHY/ITEM') T(c);    

                            declare @LOCATIONUPDATETEMPTABLE table (ID uniqueidentifier,
                                PARENTLOCATIONID uniqueidentifier,
                                HIERARCHYPATH hierarchyid);    

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

                            with LOCATION_SEQUENCE_CTE as (
                                select
                                    ID,
                                    PARENTLOCATIONID,
                                    SEQUENCE
                                from
                                    @TempTbl
                            ),
                            LOCATION_PATHS_CTE as (
                                select
                                    ID,
                                    PARENTLOCATIONID,
                                    CAST('/' + cast(SEQUENCE as nvarchar(11)) + '/' AS hierarchyid) as HIERARCHYPATH
                                from
                                    @TempTbl
                                where
                                    PARENTLOCATIONID is null                                    
                                union all    
                                select
                                    LOCATION_SEQUENCE_CTE.ID,
                                    LOCATION_SEQUENCE_CTE.PARENTLOCATIONID,
                                    CAST(HIERARCHYPATH.ToString() + cast(LOCATION_SEQUENCE_CTE.SEQUENCE as varchar(20)) + '/' AS hierarchyid)
                                from
                                    LOCATION_PATHS_CTE
                                    inner join LOCATION_SEQUENCE_CTE on LOCATION_SEQUENCE_CTE.PARENTLOCATIONID = LOCATION_PATHS_CTE.ID
                            )
                            insert into @LOCATIONUPDATETEMPTABLE (ID, PARENTLOCATIONID, HIERARCHYPATH)
                            select ID, PARENTLOCATIONID, HIERARCHYPATH
                            from LOCATION_PATHS_CTE

                            /* WI 215641/217824 AL 6/29/12  Removing cursor that changes the hierarchy fields in the sponsorship location table to temporary hierarchy ids,
                            when those ids conflict with new ids in the saved hierarchy tree.
                            There was a bug where if you had the same name for a location, but under different branches, when they were moved to the temporary branch they would share
                            the same ancestor and violate the unique name under node constraint.  This pushes everything back a node so instead of /1000001/ it will be /1000001/1/
                            Further optimizations remove the cursor and replace it with the sql statement below */

                            update SL
                            set HIERARCHYPATH =  CAST('/' + cast((100000+coln) as nvarchar(11)) + '/1/' as hierarchyid)
                            FROM (select hierarchypath,ROW_NUMBER() over (ORDER by id) coln from SPONSORSHIPLOCATION
                            where exists (select 'x' from @LOCATIONUPDATETEMPTABLE S where S.HIERARCHYPATH = SPONSORSHIPLOCATION.HIERARCHYPATH and SPONSORSHIPLOCATION.ID <> S.ID)) SL

                            update
                                dbo.SPONSORSHIPLOCATION
                            set
                                SPONSORSHIPLOCATION.HIERARCHYPATH = LOCATIONUPDATETEMPTABLE.HIERARCHYPATH,
                                SPONSORSHIPLOCATION.CHANGEDBYID = @CHANGEAGENTID,
                                SPONSORSHIPLOCATION.DATECHANGED = @CURRENTDATE
                            from
                                dbo.SPONSORSHIPLOCATION
                                inner join @LOCATIONUPDATETEMPTABLE LOCATIONUPDATETEMPTABLE on SPONSORSHIPLOCATION.ID = LOCATIONUPDATETEMPTABLE.ID
                            where --215641 adding where clause as an optimization

                                SPONSORSHIPLOCATION.HIERARCHYPATH <> LOCATIONUPDATETEMPTABLE.HIERARCHYPATH
;
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;                        
                    end