USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_UPDATEFROMXML

            CREATE procedure [dbo].[USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_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 (
                    [ID] uniqueidentifier,
                    [MEMBERSHIPCARDID] uniqueidentifier,
                    [NAMEONCARD] nvarchar(700),
                    [EXPIRATIONDATE] datetime,
                    [MEMBERID] uniqueidentifier);

                insert into @TempTbl select 
                    [ID],
                    [MEMBERSHIPCARDID],
                    [NAMEONCARD],
                    [EXPIRATIONDATE],
                    [MEMBERID]
                from dbo.UFN_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_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.[SALESORDERITEMMEMBERSHIPCARD]
                where
                    ID in
                    (
                        select
                            SALESORDERITEMMEMBERSHIPCARD.ID
                        from
                            dbo.SALESORDERITEMMEMBERSHIPCARD
                        inner join
                            dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.ID = SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID
                        where
                            SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = @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
                    dbo.[SALESORDERITEMMEMBERSHIPCARD]
                set
                    [SALESORDERITEMMEMBERSHIPCARD].[ID]=temp.[ID],
                    [SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID]=temp.[MEMBERSHIPCARDID],
                    [SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD]=temp.[NAMEONCARD],
                    [SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE]=temp.[EXPIRATIONDATE],
                    [SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID]=temp.[MEMBERID],
                    [SALESORDERITEMMEMBERSHIPCARD].CHANGEDBYID = @CHANGEAGENTID,
                    [SALESORDERITEMMEMBERSHIPCARD].DATECHANGED = @CHANGEDATE
                from
                    dbo.[SALESORDERITEMMEMBERSHIPCARD]
                inner join
                    @TempTbl as [temp] on [SALESORDERITEMMEMBERSHIPCARD].ID = [temp].ID
                where
                    ([SALESORDERITEMMEMBERSHIPCARD].[ID]<>temp.[ID]) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[ID] is null and temp.[ID] is not null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[ID] is not null and temp.[ID] is null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID]<>temp.[MEMBERSHIPCARDID]) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID] is null and temp.[MEMBERSHIPCARDID] is not null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[MEMBERSHIPCARDID] is not null and temp.[MEMBERSHIPCARDID] is null) or
                    ([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD]<>temp.[NAMEONCARD]) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD] is null and temp.[NAMEONCARD] is not null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[NAMEONCARD] is not null and temp.[NAMEONCARD] is null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE]<>temp.[EXPIRATIONDATE]) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE] is null and temp.[EXPIRATIONDATE] is not null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[EXPIRATIONDATE] is not null and temp.[EXPIRATIONDATE] is null) or
                    ([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID]<>temp.[MEMBERID]) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID] is null and temp.[MEMBERID] is not null) or 
                    ([SALESORDERITEMMEMBERSHIPCARD].[SALESORDERITEMMEMBERID] is not null and temp.[MEMBERID] is null);

                if @@Error <> 0
                    return 3;

                -- insert new items

                insert into [SALESORDERITEMMEMBERSHIPCARD] 
                (
                    [ID], 
                    [MEMBERSHIPCARDID],
                    [SALESORDERITEMMEMBERID],
                    [NAMEONCARD],
                    [EXPIRATIONDATE],                
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                )
                select
                    [ID], 
                    [MEMBERSHIPCARDID],
                    [MEMBERID],
                    [NAMEONCARD],
                    [EXPIRATIONDATE],
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CHANGEDATE, 
                    @CHANGEDATE
                from
                    @TempTbl as [temp]
                where
                    not exists (select ID from dbo.[SALESORDERITEMMEMBERSHIPCARD] as data where data.ID = [temp].ID);

                if @@Error <> 0
                    return 4;

                return 0;