USP_BATCHMEMBERSHIPROW_COMMIT

        CREATE procedure dbo.USP_BATCHMEMBERSHIPROW_COMMIT
        (
            @ID uniqueidentifier = null output,
            @MEMBERSHIPPROGRAMID uniqueidentifier = null,
            @COMMENTS nvarchar(1000) = null,
            @TRANSACTIONS xml = null,
            @MEMBERS xml = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @STATUSCODE integer = 0,
            @SENDRENEWALCODE integer = 1
        )
        as
        begin
            set nocount on;

            if @ID is null
                set @ID = newid();

            if @CHANGEAGENTID is null
                exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

            /* Extract the primary member */
            declare @CONSTITUENTID uniqueidentifier;            
            select
                @CONSTITUENTID = [MEMBERS].[ITEM].value('CONSTITUENTID[1]', 'uniqueidentifier')
            from
                @MEMBERS.nodes('/MEMBERS/ITEM') as [MEMBERS]([ITEM])
            where
                [MEMBERS].[ITEM].value('ISPRIMARY[1]', 'bit') = 1;

            declare @MAXSEQUENCE integer;
            declare @FINALTRANSACTIONEXPIRATIONDATE datetime;

            declare @TRANSACTIONSTABLE table
            (
                [MEMBERSHIPLEVELID] uniqueidentifier,
                [MEMBERSHIPLEVELTERMID] uniqueidentifier,
                [MEMBERSHIPLEVELTYPECODEID] uniqueidentifier,
                [NUMBEROFCHILDREN] integer,
                [COMMENTS] nvarchar(1000),
                [ISGIFT] bit,
                [DONORID] uniqueidentifier,
                [TRANSACTIONDATE] datetime,
                [EXPIRATIONDATE] datetime,
                [CANCELMEMBERSHIP] bit,
                [SEQUENCE] integer
            );
            insert into @TRANSACTIONSTABLE
                select
                    [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELID[1]', 'uniqueidentifier'),
                    [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELTERMID[1]', 'uniqueidentifier'),
                    [TRANSACTION].[ITEM].value('MEMBERSHIPLEVELTYPECODEID[1]', 'uniqueidentifier'),
                    [TRANSACTION].[ITEM].value('NUMBEROFCHILDREN[1]', 'integer'),
                    [TRANSACTION].[ITEM].value('COMMENTS[1]', 'nvarchar(1000)'),
                    [TRANSACTION].[ITEM].value('ISGIFT[1]', 'bit'),
                    [TRANSACTION].[ITEM].value('DONORID[1]', 'uniqueidentifier'),
                    [TRANSACTION].[ITEM].value('TRANSACTIONDATE[1]', 'datetime'),
                    [TRANSACTION].[ITEM].value('EXPIRATIONDATE[1]', 'datetime'),
                    [TRANSACTION].[ITEM].value('CANCELMEMBERSHIP[1]', 'bit'),
                    [TRANSACTION].[ITEM].value('SEQUENCE[1]', 'integer')
                from
                    @TRANSACTIONS.nodes('/TRANSACTIONS/ITEM') as [TRANSACTION](ITEM)

            select @MAXSEQUENCE = max(SEQUENCE) from @TRANSACTIONSTABLE;

            -- Get the expiration date from the last transaction to use on membership cards

            select
                @FINALTRANSACTIONEXPIRATIONDATE = EXPIRATIONDATE
            from
                @TRANSACTIONSTABLE
            where
                SEQUENCE = @MAXSEQUENCE;

            /* Retool the members collection to contain a default membership card for each member: */
            set @MEMBERS = 
            (
                select
                    coalesce([MEMBERS].[ITEM].value('../ID[1]', 'uniqueidentifier'), newid()) as [ID],
                    [MEMBERS].[ITEM].value('../CONSTITUENTID[1]', 'uniqueidentifier') as [CONSTITUENTID],
                    [MEMBERS].[ITEM].value('../ISPRIMARY[1]', 'bit') as [ISPRIMARY],
                    (
                        select
                            newid() as [ID],
                            [CONSTITUENT].[NAME] as [NAMEONCARD],
                            @FINALTRANSACTIONEXPIRATIONDATE as [EXPIRATIONDATE]
                        from
                            dbo.[CONSTITUENT]
                        where
                            [CONSTITUENT].[ID] = 
                                [MEMBERS].[ITEM].value('../CONSTITUENTID[1]', 'uniqueidentifier')
                        for xml raw('ITEM'), type, elements, root('MEMBERSHIPCARDS'), binary base64
                    )
                from
                    @MEMBERS.nodes('/MEMBERS/ITEM/CONSTITUENTID') as [MEMBERS]([ITEM])
                for xml raw('ITEM'), type, elements, root('MEMBERS'), binary base64
            );


            /*    Since members are entered on the membership level and not on the transaction level, we must create a members 
                collection with just the primary member that will be used for every transaction except for the last one, which
                must have <= the allowed number of members for the membership level associated with that transaction.    
            */
            declare @PRIMARYMEMBERXML xml =
            (
                select
                    [PRIMARYMEMBER].[ITEM].query('..')
                from
                    @MEMBERS.nodes('/MEMBERS/ITEM/ID') as [PRIMARYMEMBER]([ITEM])
                where
                    [PRIMARYMEMBER].[ITEM].value('../ISPRIMARY[1]', 'bit') = 1
                for xml raw('MEMBERS'), type, elements, binary base64
            );

            declare @MEMBERSHIPLEVELID uniqueidentifier;
            declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
            declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier;
            declare @NUMBEROFCHILDREN integer;
            declare @TRANSACTIONCOMMENTS nvarchar(1000);
            declare @ISGIFT bit;
            declare @DONORID uniqueidentifier;
            declare @TRANSACTIONDATE datetime;
            declare @EXPIRATIONDATE datetime;
            declare @SEQUENCE integer;
            declare @CURRENTMEMBERS xml = @PRIMARYMEMBERXML;
            declare @CANCELMEMBERSHIP bit;

            declare TRANSACTIONS_CURSOR cursor local fast_forward for
                select
                    [TRANSACTIONSTABLE].[MEMBERSHIPLEVELID],
                    [TRANSACTIONSTABLE].[MEMBERSHIPLEVELTERMID],
                    [TRANSACTIONSTABLE].[MEMBERSHIPLEVELTYPECODEID],
                    [TRANSACTIONSTABLE].[NUMBEROFCHILDREN],
                    [TRANSACTIONSTABLE].[COMMENTS],
                    [TRANSACTIONSTABLE].[ISGIFT],
                    [TRANSACTIONSTABLE].[DONORID],
                    [TRANSACTIONSTABLE].[TRANSACTIONDATE],
                    [TRANSACTIONSTABLE].[EXPIRATIONDATE],
                    [TRANSACTIONSTABLE].[CANCELMEMBERSHIP],
                    [TRANSACTIONSTABLE].[SEQUENCE]
                from
                    @TRANSACTIONSTABLE as [TRANSACTIONSTABLE]
                order by
                    [TRANSACTIONSTABLE].[SEQUENCE]

            open TRANSACTIONS_CURSOR;
            fetch next from TRANSACTIONS_CURSOR into
                @MEMBERSHIPLEVELID,
                @MEMBERSHIPLEVELTERMID,
                @MEMBERSHIPLEVELTYPECODEID,
                @NUMBEROFCHILDREN,
                @COMMENTS,
                @ISGIFT,
                @DONORID,
                @TRANSACTIONDATE,
                @EXPIRATIONDATE,
                @CANCELMEMBERSHIP,
                @SEQUENCE;

            begin try
                while @@fetch_status = 0
                begin
                    if @SEQUENCE = @MAXSEQUENCE -- The last item in the cursor.

                        set @CURRENTMEMBERS = @MEMBERS;

                    if @CANCELMEMBERSHIP = 1
                        exec dbo.[USP_CONSTITUENTMEMBERSHIP_CANCEL] @ID, @CHANGEAGENTID;
                    else
                        exec dbo.[USP_MEMBERSHIP_ADD]
                            @ID,
                            @CHANGEAGENTID,
                            @CONSTITUENTID,
                            @MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID,
                            @MEMBERSHIPLEVELTYPECODEID,
                            @STATUSCODE,
                            @TRANSACTIONDATE,
                            @NUMBEROFCHILDREN,
                            @COMMENTS,
                            @ISGIFT,
                            @SENDRENEWALCODE,
                            @EXPIRATIONDATE,
                            @CURRENTMEMBERS,
                            @DONORID;

                    fetch next from TRANSACTIONS_CURSOR into
                        @MEMBERSHIPLEVELID,
                        @MEMBERSHIPLEVELTERMID,
                        @MEMBERSHIPLEVELTYPECODEID,
                        @NUMBEROFCHILDREN,
                        @COMMENTS,
                        @ISGIFT,
                        @DONORID,
                        @TRANSACTIONDATE,
                        @EXPIRATIONDATE,
                        @CANCELMEMBERSHIP,
                        @SEQUENCE;
                end
            end try
            begin catch
                exec dbo.[USP_RAISE_ERROR];
                return 1;
            end catch

            close TRANSACTIONS_CURSOR;
            deallocate TRANSACTIONS_CURSOR;
        return 0;
        end