USP_EVENTGROUPMEMBER_VALIDATEMEMBERS

            CREATE procedure dbo.USP_EVENTGROUPMEMBER_VALIDATEMEMBERS(@EVENTGROUPID uniqueidentifier, @EVENTGROUPMEMBERS xml)
            with execute as caller
            as            
                declare @LEADERCOUNT int;
                declare @REGISTRANTID uniqueidentifier;
                declare @ERRORMESSAGE nvarchar(max);
                declare @MEMBERSTABLE table
                        (
                            ID int,
                            REGISTRANTID uniqueidentifier
                        );

                insert into @MEMBERSTABLE(ID, REGISTRANTID) select ROW_NUMBER() OVER (Order by ID, SEQUENCE), REGISTRANTID from dbo.UFN_EVENTGROUPMEMBER_GETMEMBERS_FROMITEMLISTXML(@EVENTGROUPMEMBERS);

                /*
                if (select count(ID) from @MEMBERSTABLE) = 0
                    raiserror('An event group must have at least one group member.',13,1);
                */

                --Verify that the registrant is only listed once in this group list

                set @REGISTRANTID = null;

                select top 1 @REGISTRANTID = REGISTRANTID from @MEMBERSTABLE as MEMBERS group by MEMBERS.REGISTRANTID having count(ID) > 1;

                if @REGISTRANTID is not null
                    begin
                        set @ERRORMESSAGE = dbo.UFN_REGISTRANT_GETNAME(@REGISTRANTID) + ' is selected as group member multiple times.';
                        raiserror(@ERRORMESSAGE, 13, 1);
                    end

                /*    
                --Make sure that at least one member is the group leader and that no more than one member is the group leader
                select @LEADERCOUNT = count(ID) from @MEMBERSTABLE as MEMBERS where MEMBERS.ISGROUPLEADER = 1;
                */

                if @LEADERCOUNT = 0
                    raiserror('At least one group member must be the group leader.',13,1);
                else if @LEADERCOUNT > 1
                    raiserror('Only one group member can be the group leader.',13,1);        

                --Make sure that a group member is not listed as a group member on another group for this event.

                set @REGISTRANTID = null;

                select 
                    top 1 @REGISTRANTID = REGISTRANTID
                from 
                    @MEMBERSTABLE as MEMBERS 
                group by 
                    REGISTRANTID
                having 
                    (select 
                        count(EXISTINGMEMBERS.ID) 
                    from 
                        dbo.EVENTGROUPMEMBER as EXISTINGMEMBERS
                    where 
                        EXISTINGMEMBERS.EVENTGROUPID <> @EVENTGROUPID and EXISTINGMEMBERS.REGISTRANTID = MEMBERS.REGISTRANTID) > 0;

                if @REGISTRANTID is not null
                    begin
                        set @ERRORMESSAGE = dbo.UFN_REGISTRANT_GETNAME(@REGISTRANTID) + ' is already on another group for this event.';
                        raiserror(@ERRORMESSAGE, 13, 1);
                    end