USP_MEMBERSHIP_GETMEMBERSHIPCARDS_UPDATEFROMXML

            CREATE procedure [dbo].[USP_MEMBERSHIP_GETMEMBERSHIPCARDS_UPDATEFROMXML] 
            (
                @MEMBERSHIPID 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,
                   [NAMEONCARD] nvarchar(700),
                   [EXPIRATIONDATE] datetime,
                   [MEMBERID] uniqueidentifier)

                insert into @TempTbl select 
                    [ID],
                    [NAMEONCARD],
                    [EXPIRATIONDATE],
                    [MEMBERID]
                from dbo.UFN_MEMBERSHIP_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

                update dbo.[MEMBERSHIPCARD] set STATUSCODE = 2 where [MEMBERSHIPCARD].ID in 
                    (select MEMBERSHIPCARD.ID from dbo.MEMBERSHIPCARD inner join dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID where MEMBER.MEMBERSHIPID = @MEMBERSHIPID
                    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;

                -- if existing membership card is printed and has an expiration date, cancel the old card and issue a new card

                update dbo.MEMBERSHIPCARD set STATUSCODE = 2
                from dbo.MEMBERSHIPCARD 
                    inner join @TempTbl TEMPTBL on TEMPTBL.ID = MEMBERSHIPCARD.ID
                where MEMBERSHIPCARD.STATUSCODE = 1 and (((MEMBERSHIPCARD.EXPIRATIONDATE is not null) and MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE) or MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD)

                update TEMPTBL set ID = newid()
                from @TempTbl TEMPTBL
                    inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
                where MEMBERSHIPCARD.STATUSCODE = 2 and (((MEMBERSHIPCARD.EXPIRATIONDATE is not null) and MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE) or MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD)

                update TEMPTBL set EXPIRATIONDATE = null
                from @TempTbl TEMPTBL
                    inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
                where MEMBERSHIPCARD.EXPIRATIONDATE is null

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

                update [MEMBERSHIPCARD]
                        set [MEMBERSHIPCARD].[ID]=temp.[ID],
                        [MEMBERSHIPCARD].[NAMEONCARD]=temp.[NAMEONCARD],
                        [MEMBERSHIPCARD].[EXPIRATIONDATE]=temp.[EXPIRATIONDATE],
                        [MEMBERSHIPCARD].[MEMBERID]=temp.[MEMBERID],
                        [MEMBERSHIPCARD].CHANGEDBYID = @CHANGEAGENTID,
                        [MEMBERSHIPCARD].DATECHANGED = @CHANGEDATE
                    from dbo.[MEMBERSHIPCARD] inner join @TempTbl as [temp] on [MEMBERSHIPCARD].ID = [temp].ID
                    where ([MEMBERSHIPCARD].[ID]<>temp.[ID]) or 
                        ([MEMBERSHIPCARD].[ID] is null and temp.[ID] is not null) or 
                        ([MEMBERSHIPCARD].[ID] is not null and temp.[ID] is null) or 
                        ([MEMBERSHIPCARD].[NAMEONCARD]<>temp.[NAMEONCARD]) or 
                        ([MEMBERSHIPCARD].[NAMEONCARD] is null and temp.[NAMEONCARD] is not null) or 
                        ([MEMBERSHIPCARD].[NAMEONCARD] is not null and temp.[NAMEONCARD] is null) or 
                        ([MEMBERSHIPCARD].[EXPIRATIONDATE]<>temp.[EXPIRATIONDATE]) or 
                        ([MEMBERSHIPCARD].[EXPIRATIONDATE] is null and temp.[EXPIRATIONDATE] is not null) or 
                        ([MEMBERSHIPCARD].[EXPIRATIONDATE] is not null and temp.[EXPIRATIONDATE] is null) or
                        ([MEMBERSHIPCARD].[MEMBERID]<>temp.[MEMBERID]) or 
                        ([MEMBERSHIPCARD].[MEMBERID] is null and temp.[MEMBERID] is not null) or 
                        ([MEMBERSHIPCARD].[MEMBERID] is not null and temp.[MEMBERID] is null)

                if @@Error <> 0
                    return 3;    

                -- insert new items

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

                if @@Error <> 0
                    return 4;

                return 0;