--SELECT *
   --FROM @ReportSet

   -- copy table variable @ReportSet into temp table #ESSReportSet because table variables cannot be altered.
   SELECT * INTO #ESSReportSet FROM @ReportSet

   -- Run the ALTER command outside of this batch, otherwise SQL will give "invalid column name" error on field det_UF_Revision
   EXEC('ALTER TABLE #ESSReportSet
     ADD det_UF_Revision    NVARCHAR(8)')

   -- populate new det_UF_Revision field with jobmatl.uf_revision
   BEGIN
     UPDATE #ESSReportSet
     SET #ESSReportSet.det_UF_Revision = jobmatl.uf_revision
     FROM #ESSReportSet ers
     -- Need to parse hdr_job using SUBSTRING and PATINDEX to get job and suffix fields
     LEFT OUTER JOIN jobmatl ON jobmatl.job = SUBSTRING(ers.hdr_job,1,PATINDEX('%-%',ers.hdr_job)-1)
          AND jobmatl.suffix = SUBSTRING(ers.hdr_job,PATINDEX('%-%',ers.hdr_job)+1,LEN(hdr_job)-PATINDEX('%-%',ers.hdr_job))
          AND jobmatl.oper_num = ers.sub_JobMatlOperNum
          AND jobmatl.sequence = ers.det_jobsequence
   END

   -- select all of the fields in the new temp table
   SELECT *
   FROM #ESSReportSet