USP_MEMBERSHIPDUESBATCH_GETMEMBERS_UPDATEFROMXML

CREATE procedure dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERS_UPDATEFROMXML
(
    @BATCHMEMBERSHIPDUESID uniqueidentifier,
    @MEMBERS xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin
    set nocount on;

    if @CHANGEAGENTID is null
        exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

    declare @BATCHMEMBERS table (ID uniqueidentifier)
    insert into @BATCHMEMBERS (ID)
    select ID
    from dbo.BATCHMEMBERSHIPDUESMEMBER
    where BATCHMEMBERSHIPDUESMEMBER.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID 
        and BATCHMEMBERSHIPDUESMEMBER.TYPECODE = 0

    insert into dbo.BATCHMEMBERSHIPDUESMEMBER (ID, BATCHMEMBERSHIPDUESID, CONSTITUENTID, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select newID(), @BATCHMEMBERSHIPDUESID, MEMBERS.CONSTITUENTID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
    from (
        select
            ID,
            CONSTITUENTID
        from dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERS_FROMITEMLISTXML(@MEMBERS)
    ) MEMBERS
    where (MEMBERS.ID is null or MEMBERS.ID not in (select ID from @BATCHMEMBERS))

    update dbo.BATCHMEMBERSHIPDUESMEMBER
    set BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID = MEMBERS.CONSTITUENTID, 
        CHANGEDBYID = @CHANGEAGENTID, 
        DATECHANGED = @CHANGEDATE
    from (
        select
            ID,
            CONSTITUENTID
        from dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERS_FROMITEMLISTXML(@MEMBERS)
    ) MEMBERS
    inner join @BATCHMEMBERS BATCHMEMBERS on MEMBERS.ID = BATCHMEMBERS.ID
    where BATCHMEMBERSHIPDUESMEMBER.ID = BATCHMEMBERS.ID

    delete from dbo.BATCHMEMBERSHIPDUESMEMBER
    where ID in (
        select BATCHMEMBERS.ID
        from @BATCHMEMBERS BATCHMEMBERS
        where ID not in (
            select
                ID
            from dbo.UFN_MEMBERSHIPDUESBATCH_GETMEMBERS_FROMITEMLISTXML(@MEMBERS)
            where ID is not null
        )
    )

end