USP_STAFFRESOURCE_CREATEJOBS

            CREATE procedure dbo.USP_STAFFRESOURCE_CREATEJOBS
            (
                @STAFFRESOURCES xml output,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as begin
                set nocount on;

                -- Setup the save information

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output    

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

                -- First turn the xml into a temp table so we can create the jobs

                declare @STAFFRESOURCESTABLE table
                (
                    FILLEDBYCODE tinyint,
                    ID uniqueidentifier,
                    JOBDESCRIPTION nvarchar(255),
                    JOBID uniqueidentifier,
                    JOBNAME nvarchar(100),
                    QUANTITYNEEDED int,
                    VOLUNTEERTYPEID uniqueidentifier
                )

                insert into @STAFFRESOURCESTABLE
                    (FILLEDBYCODE, ID, JOBDESCRIPTION, JOBID, JOBNAME, QUANTITYNEEDED, VOLUNTEERTYPEID)
                select T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE',
                        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                        T.c.value('(JOBDESCRIPTION)[1]','nvarchar(255)') AS 'JOBDESCRIPTION',
                        T.c.value('(JOBID)[1]','uniqueidentifier') AS 'JOBID',
                        T.c.value('(JOBNAME)[1]','nvarchar(100)') AS 'JOBNAME',
                        T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
                        T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID'
                from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)

                /* find any jobs that match by name and volunteer type first */
                update @STAFFRESOURCESTABLE
                    set JOBID = JOB.ID
                from dbo.JOB
                    inner join @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                        on STAFFRESOURCESTABLE.JOBNAME = JOB.NAME and 
                            STAFFRESOURCESTABLE.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID
                where ((JOBID is null) or (JOBID = '00000000-0000-0000-0000-000000000000')) and (FILLEDBYCODE = 0)

                /* Finally check that there is only one new job that is going to be created per job name and volunteer type combo */
                update @STAFFRESOURCESTABLE
                    set JOBDESCRIPTION = TOPJOBNAME.JOBDESCRIPTION
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                    inner join (select DISTINCTJOBDESCRIPTION.JOBNAME, 
                                        DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID,
                                        (select top 1 TOPJOBDESCRIPTION.JOBDESCRIPTION
                                            from @STAFFRESOURCESTABLE as TOPJOBDESCRIPTION
                                            where (TOPJOBDESCRIPTION.JOBNAME = DISTINCTJOBDESCRIPTION.JOBNAME) and
                                                    (TOPJOBDESCRIPTION.VOLUNTEERTYPEID = DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID)) as JOBDESCRIPTION
                                from @STAFFRESOURCESTABLE as DISTINCTJOBDESCRIPTION) as TOPJOBNAME
                        on TOPJOBNAME.JOBNAME = STAFFRESOURCESTABLE.JOBNAME and TOPJOBNAME.VOLUNTEERTYPEID = STAFFRESOURCESTABLE.VOLUNTEERTYPEID
                where ((STAFFRESOURCESTABLE.JOBID is null) or (STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000')) and (STAFFRESOURCESTABLE.FILLEDBYCODE = 0)

              declare @DISTINCTJOB table
              (
                JOBID uniqueidentifier,
                JOBNAME nvarchar(100),
                JOBDESCRIPTION nvarchar(255)
              );

              insert into @DISTINCTJOB
                (JOBNAME, JOBDESCRIPTION)
              select distinct JOBNAME, JOBDESCRIPTION
              from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
              where ((STAFFRESOURCESTABLE.JOBID is null) or (STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000')) and (STAFFRESOURCESTABLE.FILLEDBYCODE = 0) 

              update @DISTINCTJOB
                set JOBID = NEWID();

              update @STAFFRESOURCESTABLE
                set JOBID = DISTINCTJOB.JOBID
              from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                inner join @DISTINCTJOB as DISTINCTJOB
                    on STAFFRESOURCESTABLE.JOBNAME = DISTINCTJOB.JOBNAME and STAFFRESOURCESTABLE.JOBDESCRIPTION = DISTINCTJOB.JOBDESCRIPTION;

                -- Now create the jobs needed

                insert into dbo.JOB
                    (ID, 
                    NAME, 
                    DESCRIPTION, 
                    VOLUNTEERTYPEID, 
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED)
                select distinct STAFFRESOURCESTABLE.JOBID, 
                        STAFFRESOURCESTABLE.JOBNAME, 
                        STAFFRESOURCESTABLE.JOBDESCRIPTION, 
                        STAFFRESOURCESTABLE.VOLUNTEERTYPEID, 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                    left outer join dbo.JOB
                        on STAFFRESOURCESTABLE.JOBID = JOB.ID 
                where (JOB.ID is null) and (STAFFRESOURCESTABLE.FILLEDBYCODE = 0);

                -- Now recreate the XML from the temp table and setup the return value

                set @STAFFRESOURCES = (select [FILLEDBYCODE], [ID], [JOBDESCRIPTION], [JOBID], [JOBNAME], [QUANTITYNEEDED], [VOLUNTEERTYPEID]
                                        from @STAFFRESOURCESTABLE
                                        for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),BINARY BASE64)
            end