USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_ADDFROMXML

            CREATE procedure [dbo].[USP_SALESORDERITEMMEMBERSHIP_GETMEMBERSHIPCARDS_ADDFROMXML] 
            (
                @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;

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

                if @@Error <> 0
                    return 2;

                return 0;