USP_PROSPECTPLAN_SECONDARYFUNDRAISERS_CUSTOMUPDATEFROMXML

            CREATE PROCEDURE dbo.USP_PROSPECTPLAN_SECONDARYFUNDRAISERS_CUSTOMUPDATEFROMXML   
            (  
            @PROSPECTPLANID uniqueidentifier,  
            @XML xml,  
            @CHANGEAGENTID uniqueidentifier = null,  
            @CHANGEDATE datetime = null  
            )  

            as  

            set nocount on;  

            if @CHANGEAGENTID is null  
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output  

            if @CHANGEDATE is null   
                set @CHANGEDATE = getdate()  

            -- build a temporary table containing the values from the XML  

            declare @TempTbl table (  
                [FUNDRAISERID] uniqueidentifier,  
                [ID] uniqueidentifier,  
                [SEQUENCE] int,  
                [SOLICITORROLECODEID] uniqueidentifier,
                [DATEFROM] datetime,
                [DATETO] datetime)  

            insert into @TempTbl select   
                [FUNDRAISERID],  
                [ID],  
                [SEQUENCE],  
                [SOLICITORROLECODEID],
                [DATEFROM] datetime,
                [DATETO] datetime  
            from dbo.UFN_PROSPECTPLAN_SECONDARYFUNDRAISERS_FROMITEMLISTXML(@XML)  

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');  

            if @@Error <> 0  
                return 1;  

            declare @contextCache varbinary(128);  
            declare @e int;  

            -- cache current context information   

            set @contextCache = CONTEXT_INFO();  

            -- set CONTEXT_INFO to @CHANGEAGENTID   

            if not @CHANGEAGENTID is null  
                set CONTEXT_INFO @CHANGEAGENTID;  

            -- delete any items that no longer exist in the XML table  

            update dbo.PROSPECTASSIGNMENTREQUESTPROSPECTPLAN
            set SECONDARYFUNDRAISERID = null
            where SECONDARYFUNDRAISERID in
                (select ID from dbo.UFN_PROSPECTPLAN_SECONDARYFUNDRAISERS(@PROSPECTPLANID)  
                    except select ID from @TempTbl)   


            delete from dbo.[SECONDARYFUNDRAISER] where [SECONDARYFUNDRAISER].ID in   
                (select ID from dbo.UFN_PROSPECTPLAN_SECONDARYFUNDRAISERS(@PROSPECTPLANID)  
                    EXCEPT select ID from @TempTbl)   

            -- delete any items that no longer exist in the XML table  

            delete from dbo.[SECONDARYFUNDRAISER] where [SECONDARYFUNDRAISER].ID in   
                (select ID from dbo.UFN_PROSPECTPLAN_SECONDARYFUNDRAISERS(@PROSPECTPLANID)  
                    except select ID from @TempTbl)   

            select @e=@@error;  

            -- reset CONTEXT_INFO to previous value   

            if not @contextCache is null  
                set CONTEXT_INFO @contextCache;  

            if @e <> 0  
                return 2;  

            -- update the items that exist in the XML table and the db  

            update dbo.[SECONDARYFUNDRAISER]  
            set [SECONDARYFUNDRAISER].[FUNDRAISERID]=temp.[FUNDRAISERID],  
                [SECONDARYFUNDRAISER].[ID]=temp.[ID],  
                [SECONDARYFUNDRAISER].[SEQUENCE]=temp.[SEQUENCE],  
                [SECONDARYFUNDRAISER].[SOLICITORROLECODEID]=temp.[SOLICITORROLECODEID],
                [SECONDARYFUNDRAISER].DATEFROM = temp.[DATEFROM],
                [SECONDARYFUNDRAISER].DATETO = temp.[DATETO],
                [SECONDARYFUNDRAISER].CHANGEDBYID = @CHANGEAGENTID,  
                [SECONDARYFUNDRAISER].DATECHANGED = @CHANGEDATE
            from dbo.[SECONDARYFUNDRAISER] inner join @TempTbl as [temp] on [SECONDARYFUNDRAISER].ID = [temp].ID  
            where 
                ([SECONDARYFUNDRAISER].[FUNDRAISERID]<>temp.[FUNDRAISERID]) or   
                ([SECONDARYFUNDRAISER].[FUNDRAISERID] is null and temp.[FUNDRAISERID] is not null) or   
                ([SECONDARYFUNDRAISER].[FUNDRAISERID] is not null and temp.[FUNDRAISERID] is null) or   
                ([SECONDARYFUNDRAISER].[ID]<>temp.[ID]) or   
                ([SECONDARYFUNDRAISER].[ID] is null and temp.[ID] is not null) or   
                ([SECONDARYFUNDRAISER].[ID] is not null and temp.[ID] is null) or   
                ([SECONDARYFUNDRAISER].[SEQUENCE]<>temp.[SEQUENCE]) or   
                ([SECONDARYFUNDRAISER].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or   
                ([SECONDARYFUNDRAISER].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or   
                ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID]<>temp.[SOLICITORROLECODEID]) or   
                ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID] is null and temp.[SOLICITORROLECODEID] is not null) or   
                ([SECONDARYFUNDRAISER].[SOLICITORROLECODEID] is not null and temp.[SOLICITORROLECODEID] is null) or
                ([SECONDARYFUNDRAISER].[DATEFROM]<>temp.[DATEFROM]) or   
                ([SECONDARYFUNDRAISER].[DATEFROM] is null and temp.[DATEFROM] is not null) or   
                ([SECONDARYFUNDRAISER].[DATEFROM] is not null and temp.[DATEFROM] is null) or
                ([SECONDARYFUNDRAISER].[DATETO]<>temp.[DATETO]) or   
                ([SECONDARYFUNDRAISER].[DATETO] is null and temp.[DATETO] is not null) or   
                ([SECONDARYFUNDRAISER].[DATETO] is not null and temp.[DATETO] is null) 

            if @@Error <> 0  
             return 3;   

            -- insert new items  

            insert into dbo.[SECONDARYFUNDRAISER]   
                ([PROSPECTPLANID],   
                [FUNDRAISERID],  
                [ID],  
                [SEQUENCE],  
                [SOLICITORROLECODEID],
                [DATEFROM],
                [DATETO],
                ADDEDBYID,   
                CHANGEDBYID,   
                DATEADDED,   
                DATECHANGED)  
            select @PROSPECTPLANID,   
                [FUNDRAISERID],  
                [ID],  
                [SEQUENCE],  
                [SOLICITORROLECODEID],  
                [DATEFROM],
                [DATETO],
                @CHANGEAGENTID,   
                @CHANGEAGENTID,   
                @CHANGEDATE,   
                @CHANGEDATE  
            from @TempTbl as [temp]  
            where not exists (select ID from dbo.[SECONDARYFUNDRAISER] as data where data.ID = [temp].ID)  

            if @@Error <> 0  
             return 4;  

            return 0;