USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_ADDFROMXML

CREATE procedure dbo.USP_SALESORDERITEMMEMBERSHIP_GETMEMBERS_ADDFROMXML
(
    @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;

    -- 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];

    if @@Error <> 0
        return 2;

    return 0;