USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_UPDATEFROMXML

CREATE procedure dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_UPDATEFROMXML
(
    @SALESORDERITEMMEMBERSHIPID 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 (
       [CONSTITUENTID] uniqueidentifier,
       [ID] uniqueidentifier,
       [ISCAREGIVER] bit,
       [ISPRIMARY] bit,
       [MEMBERID] uniqueidentifier)

    insert into @TempTbl select 
        [CONSTITUENTID],
        [ID],
        isnull([ISCAREGIVER], 0),
        [ISPRIMARY],
        [MEMBERID] 
    from dbo.UFN_SALESORDERITEMMEMBERSHIP_GETMEMBERS_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

    delete from dbo.[SALESORDERITEMMEMBER] where [SALESORDERITEMMEMBER].ID in 
        (select ID from dbo.UFN_SALESORDERITEMMEMBERSHIP_GETMEMBERS
        (
            @SALESORDERITEMMEMBERSHIPID
        )
        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 [SALESORDERITEMMEMBER]
            set [SALESORDERITEMMEMBER].[CONSTITUENTID]=temp.[CONSTITUENTID],
            [SALESORDERITEMMEMBER].[ID]=temp.[ID],
            [SALESORDERITEMMEMBER].[ISCAREGIVER]=temp.[ISCAREGIVER],
            [SALESORDERITEMMEMBER].[ISPRIMARY]=temp.[ISPRIMARY],
            [SALESORDERITEMMEMBER].[MEMBERID]=temp.[MEMBERID],
            [SALESORDERITEMMEMBER].CHANGEDBYID = @CHANGEAGENTID,
            [SALESORDERITEMMEMBER].DATECHANGED = @CHANGEDATE

        from dbo.[SALESORDERITEMMEMBER] inner join @TempTbl as [temp] on [SALESORDERITEMMEMBER].ID = [temp].ID
        where ([SALESORDERITEMMEMBER].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or 
            ([SALESORDERITEMMEMBER].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or 
            ([SALESORDERITEMMEMBER].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or 
            ([SALESORDERITEMMEMBER].[ID]<>temp.[ID]) or 
            ([SALESORDERITEMMEMBER].[ID] is null and temp.[ID] is not null) or 
            ([SALESORDERITEMMEMBER].[ID] is not null and temp.[ID] is null) or 
            ([SALESORDERITEMMEMBER].[ISCAREGIVER]<>temp.[ISCAREGIVER]) or 
            ([SALESORDERITEMMEMBER].[ISCAREGIVER] is null and temp.[ISCAREGIVER] is not null) or 
            ([SALESORDERITEMMEMBER].[ISCAREGIVER] is not null and temp.[ISCAREGIVER] is null) or 
            ([SALESORDERITEMMEMBER].[ISPRIMARY]<>temp.[ISPRIMARY]) or 
            ([SALESORDERITEMMEMBER].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or 
            ([SALESORDERITEMMEMBER].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null) or 
            ([SALESORDERITEMMEMBER].[MEMBERID]<>temp.[MEMBERID]) or 
            ([SALESORDERITEMMEMBER].[MEMBERID] is null and temp.[MEMBERID] is not null) or 
            ([SALESORDERITEMMEMBER].[MEMBERID] is not null and temp.[MEMBERID] is null)

    if @@Error <> 0
        return 3;    

    -- insert new items

    insert into [SALESORDERITEMMEMBER] 
        ([SALESORDERITEMMEMBERSHIPID], 
        [CONSTITUENTID],
        [ID],
        [ISCAREGIVER],
        [ISPRIMARY],
        [MEMBERID],                
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
    select @SALESORDERITEMMEMBERSHIPID, 
        [CONSTITUENTID],
        [ID],
        [ISCAREGIVER],
        [ISPRIMARY],
        [MEMBERID], 
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CHANGEDATE, 
        @CHANGEDATE
    from @TempTbl as [temp]
    where not exists (select ID from dbo.[SALESORDERITEMMEMBER] as data where data.ID = [temp].ID)

    if @@Error <> 0
        return 4;

    return 0;