USP_STAFFRESOURCE_PROCESSVOLUNTEERS

            CREATE procedure dbo.USP_STAFFRESOURCE_PROCESSVOLUNTEERS
            (
                @VOLUNTEERS xml,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            begin
                /* Get some extra data to perform an insert */
                if (@CHANGEAGENTID is null)
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @VOLUNTEERSTOADD table 
                (
                    CONSTITUENTID uniqueidentifier
                );

                /* Pull from the VOLUNTEERS node first */
                insert into @VOLUNTEERSTOADD
                (
                    CONSTITUENTID
                )
                select 
                    NEWVOLUNTEER.VOLUNTEERID
                from 
                (
                    select T.c.value('(VOLUNTEERID)[1]','uniqueidentifier') AS VOLUNTEERID
                    from @VOLUNTEERS.nodes('/VOLUNTEERS/ITEM') T(c)
                ) as NEWVOLUNTEER
                left outer join dbo.VOLUNTEER on 
                    NEWVOLUNTEER.VOLUNTEERID = VOLUNTEER.ID
                left outer join dbo.VOLUNTEERDATERANGE on 
                    VOLUNTEERDATERANGE.CONSTITUENTID = NEWVOLUNTEER.VOLUNTEERID
                where 
                    VOLUNTEER.ID is null and 
                    VOLUNTEERDATERANGE.ID is null and 
                    NEWVOLUNTEER.VOLUNTEERID is not null;

                /* Next from assignments */
                insert into @VOLUNTEERSTOADD
                (
                    CONSTITUENTID
                )
                select 
                    NEWVOLUNTEER.VOLUNTEERID
                from 
                (
                    select T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS VOLUNTEERID
                    from @VOLUNTEERS.nodes('/ASSIGNMENTS/ITEM') T(c)
                ) as NEWVOLUNTEER
                left outer join dbo.VOLUNTEER on 
                    NEWVOLUNTEER.VOLUNTEERID = VOLUNTEER.ID
                left outer join dbo.VOLUNTEERDATERANGE on 
                    VOLUNTEERDATERANGE.CONSTITUENTID = NEWVOLUNTEER.VOLUNTEERID
                left outer join @VOLUNTEERSTOADD as VOLUNTEERSTOADD on 
                    VOLUNTEERSTOADD.CONSTITUENTID = NEWVOLUNTEER.VOLUNTEERID
                where 
                    VOLUNTEER.ID is null and 
                    VOLUNTEERDATERANGE.ID is null and 
                    NEWVOLUNTEER.VOLUNTEERID is not null and
                    VOLUNTEERSTOADD.CONSTITUENTID is null;

                /* Last from staff assignments */
                insert into @VOLUNTEERSTOADD
                (
                    CONSTITUENTID
                )
                select 
                    NEWVOLUNTEER.VOLUNTEERID
                from 
                (
                    select T.c.value('(VOLUNTEERID)[1]','uniqueidentifier') AS VOLUNTEERID
                    from @VOLUNTEERS.nodes('/ASSIGNEDSTAFF/ITEM') T(c)
                ) as NEWVOLUNTEER
                left outer join dbo.VOLUNTEER on 
                    NEWVOLUNTEER.VOLUNTEERID = VOLUNTEER.ID
                left outer join dbo.VOLUNTEERDATERANGE on 
                    VOLUNTEERDATERANGE.CONSTITUENTID = NEWVOLUNTEER.VOLUNTEERID
                left outer join @VOLUNTEERSTOADD as VOLUNTEERSTOADD on 
                    VOLUNTEERSTOADD.CONSTITUENTID = NEWVOLUNTEER.VOLUNTEERID
                where 
                    VOLUNTEER.ID is null and 
                    VOLUNTEERDATERANGE.ID is null and 
                    NEWVOLUNTEER.VOLUNTEERID is not null and
                    NEWVOLUNTEER.VOLUNTEERID != '00000000-0000-0000-0000-000000000000' and
                    VOLUNTEERSTOADD.CONSTITUENTID is null;

                /* Now create a volunteer record for anyone who is not already a volunteer */
                insert into dbo.VOLUNTEER
                (
                    ID, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select 
                    VOLUNTEERSTOADD.CONSTITUENTID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @VOLUNTEERSTOADD as VOLUNTEERSTOADD;

                /* Also need to create a volunteer date range record.  Not sure why both of these exist. */
                insert into dbo.VOLUNTEERDATERANGE
                (
                    ID, 
                    CONSTITUENTID, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select 
                    newid(),
                    VOLUNTEERSTOADD.CONSTITUENTID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @VOLUNTEERSTOADD as VOLUNTEERSTOADD;

            end