存储过程

USE [TraceabilityDB]GO/****** Object:  Trigger [dbo].[T_COILINFO_RECORD]    Script Date: 02/06/2014 11:27:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*--------------------------------------------------開始終了報告テーブルINSERT時の処理  ・開始終了テーブルをオーダーNo, コイルNoごとに展開してコイル実績情報表示テーブルを更新  ・開始終了テーブルをオーダーNo, コイルNoごとに展開してコイル状態テーブルを更新--------------------------------------------------*/ALTER TRIGGER [dbo].[T_COILINFO_RECORD] ON [dbo].[T_STARTENDDATA] AFTER INSERTASSET NOCOUNT ONDECLARE @iINT-- ループカウンタDECLARE @coilCountINT-- コイル数DECLARE @messageNVARCHAR(MAX)-- エラーメッセージバッファDECLARE @orderNoNVARCHAR(10)-- オーダーNoバッファDECLARE @coilNoNVARCHAR(4)-- コイルNoバッファDECLARE @serialNoNVARCHAR(14)-- シリアルNoバッファDECLARE @countINT-- 既存レコード件数バッファDECLARE @startTimeNVARCHAR(16)-- 開始日時バッファDECLARE @endTimeNVARCHAR(16)-- 終了日時バッファ--T_STARTENDDATA fieldsDECLARE @ID intDECLARE @SEQUENCENO bigintDECLARE @EQUIPMENTCODE nvarchar(4)DECLARE @EQUIPMENTDETAILCODE1 nvarchar(4)DECLARE @EQUIPMENTDETAILCODE2 nvarchar(4)DECLARE @SENDYEAR intDECLARE @SENDMONTH intDECLARE @SENDDAY intDECLARE @SENDHOUR intDECLARE @SENDMINUTE intDECLARE @STARTENDFLG intDECLARE @ORDERNO1 nvarchar(10)DECLARE @COILNO1 nvarchar(4)DECLARE @ORDERNO2 nvarchar(10)DECLARE @COILNO2 nvarchar(4)DECLARE @ORDERNO3 nvarchar(10)DECLARE @COILNO3 nvarchar(4)DECLARE @ORDERNO4 nvarchar(10)DECLARE @COILNO4 nvarchar(4)DECLARE @ORDERNO5 nvarchar(10)DECLARE @COILNO5 nvarchar(4)DECLARE @ORDERNO6 nvarchar(10)DECLARE @COILNO6 nvarchar(4)DECLARE @ORDERNO7 nvarchar(10)DECLARE @COILNO7 nvarchar(4)DECLARE @ORDERNO8 nvarchar(10)DECLARE @COILNO8 nvarchar(4)DECLARE @ORDERNO9 nvarchar(10)DECLARE @COILNO9 nvarchar(4)DECLARE @ORDERNO10 nvarchar(10)DECLARE @COILNO10 nvarchar(4)DECLARE @ORDERNO11 nvarchar(10)DECLARE @COILNO11 nvarchar(4)DECLARE @ORDERNO12 nvarchar(10)DECLARE @COILNO12 nvarchar(4)DECLARE @ORDERNO13 nvarchar(10)DECLARE @COILNO13 nvarchar(4)DECLARE @ORDERNO14 nvarchar(10)DECLARE @COILNO14 nvarchar(4)DECLARE @ORDERNO15 nvarchar(10)DECLARE @COILNO15 nvarchar(4)DECLARE @ORDERNO16 nvarchar(10)DECLARE @COILNO16 nvarchar(4)DECLARE @ORDERNO17 nvarchar(10)DECLARE @COILNO17 nvarchar(4)DECLARE @ORDERNO18 nvarchar(10)DECLARE @COILNO18 nvarchar(4)DECLARE @ORDERNO19 nvarchar(10)DECLARE @COILNO19 nvarchar(4)DECLARE @ORDERNO20 nvarchar(10)DECLARE @COILNO20 nvarchar(4)DECLARE @ORDERNO21 nvarchar(10)DECLARE @COILNO21 nvarchar(4)DECLARE @ORDERNO22 nvarchar(10)DECLARE @COILNO22 nvarchar(4)DECLARE @ORDERNO23 nvarchar(10)DECLARE @COILNO23 nvarchar(4)DECLARE @ORDERNO24 nvarchar(10)DECLARE @COILNO24 nvarchar(4)DECLARE @ORDERNO25 nvarchar(10)DECLARE @COILNO25 nvarchar(4)DECLARE @ORDERNO26 nvarchar(10)DECLARE @COILNO26 nvarchar(4)DECLARE @ORDERNO27 nvarchar(10)DECLARE @COILNO27 nvarchar(4)DECLARE @ORDERNO28 nvarchar(10)DECLARE @COILNO28 nvarchar(4)DECLARE @ORDERNO29 nvarchar(10)DECLARE @COILNO29 nvarchar(4)DECLARE @ORDERNO30 nvarchar(10)DECLARE @COILNO30 nvarchar(4)DECLARE @ORDERNO31 nvarchar(10)DECLARE @COILNO31 nvarchar(4)DECLARE @ORDERNO32 nvarchar(10)DECLARE @COILNO32 nvarchar(4)DECLARE @ORDERNO33 nvarchar(10)DECLARE @COILNO33 nvarchar(4)DECLARE @ORDERNO34 nvarchar(10)DECLARE @COILNO34 nvarchar(4)DECLARE @ORDERNO35 nvarchar(10)DECLARE @COILNO35 nvarchar(4)DECLARE @ORDERNO36 nvarchar(10)DECLARE @COILNO36 nvarchar(4)DECLARE @ORDERNO37 nvarchar(10)DECLARE @COILNO37 nvarchar(4)DECLARE @ORDERNO38 nvarchar(10)DECLARE @COILNO38 nvarchar(4)DECLARE @ORDERNO39 nvarchar(10)DECLARE @COILNO39 nvarchar(4)DECLARE @ORDERNO40 nvarchar(10)DECLARE @COILNO40 nvarchar(4)DECLARE @ORDERNO41 nvarchar(10)DECLARE @COILNO41 nvarchar(4)DECLARE @ORDERNO42 nvarchar(10)DECLARE @COILNO42 nvarchar(4)DECLARE @ORDERNO43 nvarchar(10)DECLARE @COILNO43 nvarchar(4)DECLARE @ORDERNO44 nvarchar(10)DECLARE @COILNO44 nvarchar(4)DECLARE @ORDERNO45 nvarchar(10)DECLARE @COILNO45 nvarchar(4)DECLARE @ORDERNO46 nvarchar(10)DECLARE @COILNO46 nvarchar(4)DECLARE @ORDERNO47 nvarchar(10)DECLARE @COILNO47 nvarchar(4)DECLARE @ORDERNO48 nvarchar(10)DECLARE @COILNO48 nvarchar(4)DECLARE @ORDERNO49 nvarchar(10)DECLARE @COILNO49 nvarchar(4)DECLARE @ORDERNO50 nvarchar(10)DECLARE @COILNO50 nvarchar(4)DECLARE @ORDERNO51 nvarchar(10)DECLARE @COILNO51 nvarchar(4)DECLARE @ORDERNO52 nvarchar(10)DECLARE @COILNO52 nvarchar(4)DECLARE @ORDERNO53 nvarchar(10)DECLARE @COILNO53 nvarchar(4)DECLARE @ORDERNO54 nvarchar(10)DECLARE @COILNO54 nvarchar(4)DECLARE @ORDERNO55 nvarchar(10)DECLARE @COILNO55 nvarchar(4)DECLARE @ORDERNO56 nvarchar(10)DECLARE @COILNO56 nvarchar(4)DECLARE @ORDERNO57 nvarchar(10)DECLARE @COILNO57 nvarchar(4)DECLARE @ORDERNO58 nvarchar(10)DECLARE @COILNO58 nvarchar(4)DECLARE @ORDERNO59 nvarchar(10)DECLARE @COILNO59 nvarchar(4)DECLARE @ORDERNO60 nvarchar(10)DECLARE @COILNO60 nvarchar(4)DECLARE @PROCESSCOUNT intDECLARE @PROCESSSTEP nvarchar(20)DECLARE @BARCODEFLG intDECLARE @WORKERCODE1 nvarchar(10)DECLARE @WORKERCODE2 nvarchar(10)DECLARE @WORKERCODE3 nvarchar(10)DECLARE @WORKERCODE4 nvarchar(10)DECLARE @WORKERCODE5 nvarchar(10)DECLARE @ITEMNO1 intDECLARE @ITEMNO2 intDECLARE @ITEMNO3 intDECLARE @ITEMNO4 intDECLARE @ITEMNO5 intDECLARE @ITEMNO6 intDECLARE @ITEMNO7 intDECLARE @ITEMNO8 intDECLARE @ITEMNO9 intDECLARE @ITEMNO10 intDECLARE @ITEMNO11 intDECLARE @ITEMNO12 intDECLARE @ITEMNO13 intDECLARE @ITEMNO14 intDECLARE @ITEMNO15 intDECLARE @ITEMNO16 intDECLARE @ITEMNO17 intDECLARE @ITEMNO18 intDECLARE @ITEMNO19 intDECLARE @ITEMNO20 intDECLARE @VALUE1 intDECLARE @VALUE2 intDECLARE @VALUE3 intDECLARE @VALUE4 intDECLARE @VALUE5 intDECLARE @VALUE6 intDECLARE @VALUE7 intDECLARE @VALUE8 intDECLARE @VALUE9 intDECLARE @VALUE10 intDECLARE @VALUE11 nvarchar(10)DECLARE @VALUE12 nvarchar(10)DECLARE @VALUE13 nvarchar(10)DECLARE @VALUE14 nvarchar(10)DECLARE @VALUE15 nvarchar(10)DECLARE @VALUE16 nvarchar(10)DECLARE @VALUE17 nvarchar(10)DECLARE @VALUE18 nvarchar(10)DECLARE @VALUE19 nvarchar(10)DECLARE @VALUE20 nvarchar(10)/*最新ID取得*/SET @ID = (SELECT MAX(ID) FROM T_STARTENDDATA)/*レコード取得*/DECLARE StartEndCur CURSOR FORSELECTEQUIPMENTCODE, EQUIPMENTDETAILCODE1, EQUIPMENTDETAILCODE2, SENDYEAR, SENDMONTH, SENDDAY, SENDHOUR, SENDMINUTE, STARTENDFLG, ORDERNO1, COILNO1, ORDERNO2, COILNO2, ORDERNO3, COILNO3, ORDERNO4, COILNO4, ORDERNO5, COILNO5, ORDERNO6, COILNO6, ORDERNO7, COILNO7, ORDERNO8, COILNO8, ORDERNO9, COILNO9, ORDERNO10, COILNO10, ORDERNO11, COILNO11, ORDERNO12, COILNO12, ORDERNO13, COILNO13, ORDERNO14, COILNO14, ORDERNO15, COILNO15, ORDERNO16, COILNO16, ORDERNO17, COILNO17, ORDERNO18, COILNO18, ORDERNO19, COILNO19, ORDERNO20, COILNO20, ORDERNO21, COILNO21, ORDERNO22, COILNO22, ORDERNO23, COILNO23, ORDERNO24, COILNO24, ORDERNO25, COILNO25, ORDERNO26, COILNO26, ORDERNO27, COILNO27, ORDERNO28, COILNO28, ORDERNO29, COILNO29, ORDERNO30, COILNO30, ORDERNO31, COILNO31, ORDERNO32, COILNO32, ORDERNO33, COILNO33, ORDERNO34, COILNO34, ORDERNO35, COILNO35, ORDERNO36, COILNO36, ORDERNO37, COILNO37, ORDERNO38, COILNO38, ORDERNO39, COILNO39, ORDERNO40, COILNO40, ORDERNO41, COILNO41, ORDERNO42, COILNO42, ORDERNO43, COILNO43, ORDERNO44, COILNO44, ORDERNO45, COILNO45, ORDERNO46, COILNO46, ORDERNO47, COILNO47, ORDERNO48, COILNO48, ORDERNO49, COILNO49, ORDERNO50, COILNO50, ORDERNO51, COILNO51, ORDERNO52, COILNO52, ORDERNO53, COILNO53, ORDERNO54, COILNO54, ORDERNO55, COILNO55, ORDERNO56, COILNO56, ORDERNO57, COILNO57, ORDERNO58, COILNO58, ORDERNO59, COILNO59, ORDERNO60, COILNO60, PROCESSCOUNT, PROCESSSTEP, BARCODEFLG, WORKERCODE1, WORKERCODE2, WORKERCODE3, WORKERCODE4, WORKERCODE5, ITEMNO1, VALUE1, ITEMNO2, VALUE2, ITEMNO3, VALUE3, ITEMNO4, VALUE4, ITEMNO5, VALUE5, ITEMNO6, VALUE6, ITEMNO7, VALUE7, ITEMNO8, VALUE8, ITEMNO9, VALUE9, ITEMNO10, VALUE10, ITEMNO11, VALUE11, ITEMNO12, VALUE12, ITEMNO13, VALUE13, ITEMNO14, VALUE14, ITEMNO15, VALUE15, ITEMNO16, VALUE16, ITEMNO17, VALUE17, ITEMNO18, VALUE18, ITEMNO19, VALUE19, ITEMNO20, VALUE20FROMT_STARTENDDATAWHEREID = @IDBEGIN TRYBEGIN TRANSACTIONOPEN StartEndCurFETCH NEXT FROM StartEndCur INTO@EQUIPMENTCODE, @EQUIPMENTDETAILCODE1, @EQUIPMENTDETAILCODE2, @SENDYEAR, @SENDMONTH, @SENDDAY, @SENDHOUR, @SENDMINUTE, @STARTENDFLG, @ORDERNO1, @COILNO1, @ORDERNO2, @COILNO2, @ORDERNO3, @COILNO3, @ORDERNO4, @COILNO4, @ORDERNO5, @COILNO5, @ORDERNO6, @COILNO6, @ORDERNO7, @COILNO7, @ORDERNO8, @COILNO8, @ORDERNO9, @COILNO9, @ORDERNO10, @COILNO10, @ORDERNO11, @COILNO11, @ORDERNO12, @COILNO12, @ORDERNO13, @COILNO13, @ORDERNO14, @COILNO14, @ORDERNO15, @COILNO15, @ORDERNO16, @COILNO16, @ORDERNO17, @COILNO17, @ORDERNO18, @COILNO18, @ORDERNO19, @COILNO19, @ORDERNO20, @COILNO20, @ORDERNO21, @COILNO21, @ORDERNO22, @COILNO22, @ORDERNO23, @COILNO23, @ORDERNO24, @COILNO24, @ORDERNO25, @COILNO25, @ORDERNO26, @COILNO26, @ORDERNO27, @COILNO27, @ORDERNO28, @COILNO28, @ORDERNO29, @COILNO29, @ORDERNO30, @COILNO30, @ORDERNO31, @COILNO31, @ORDERNO32, @COILNO32, @ORDERNO33, @COILNO33, @ORDERNO34, @COILNO34, @ORDERNO35, @COILNO35, @ORDERNO36, @COILNO36, @ORDERNO37, @COILNO37, @ORDERNO38, @COILNO38, @ORDERNO39, @COILNO39, @ORDERNO40, @COILNO40, @ORDERNO41, @COILNO41, @ORDERNO42, @COILNO42, @ORDERNO43, @COILNO43, @ORDERNO44, @COILNO44, @ORDERNO45, @COILNO45, @ORDERNO46, @COILNO46, @ORDERNO47, @COILNO47, @ORDERNO48, @COILNO48, @ORDERNO49, @COILNO49, @ORDERNO50, @COILNO50, @ORDERNO51, @COILNO51, @ORDERNO52, @COILNO52, @ORDERNO53, @COILNO53, @ORDERNO54, @COILNO54, @ORDERNO55, @COILNO55, @ORDERNO56, @COILNO56, @ORDERNO57, @COILNO57, @ORDERNO58, @COILNO58, @ORDERNO59, @COILNO59, @ORDERNO60, @COILNO60, @PROCESSCOUNT, @PROCESSSTEP, @BARCODEFLG, @WORKERCODE1, @WORKERCODE2, @WORKERCODE3, @WORKERCODE4, @WORKERCODE5, @ITEMNO1, @VALUE1, @ITEMNO2, @VALUE2, @ITEMNO3, @VALUE3, @ITEMNO4, @VALUE4, @ITEMNO5, @VALUE5, @ITEMNO6, @VALUE6, @ITEMNO7, @VALUE7, @ITEMNO8, @VALUE8, @ITEMNO9, @VALUE9, @ITEMNO10, @VALUE10, @ITEMNO11, @VALUE11, @ITEMNO12, @VALUE12, @ITEMNO13, @VALUE13, @ITEMNO14, @VALUE14, @ITEMNO15, @VALUE15, @ITEMNO16, @VALUE16, @ITEMNO17, @VALUE17, @ITEMNO18, @VALUE18, @ITEMNO19, @VALUE19, @ITEMNO20, @VALUE20--バーコードフラグ無のチェックIF (@BARCODEFLG = 0)BEGINSET @message = 'バーコード無し'PRINT(@message)CLOSE StartEndCurDEALLOCATE StartEndCurCOMMIT TRANSACTIONRETURNEND--開始/終了日時IF (@STARTENDFLG = 0)BEGINSET @startTime = CAST(RIGHT('0000' +CAST(@SENDYEAR AS VARCHAR), 4) AS NVARCHAR) + '/' +CAST(RIGHT('00' +CAST(@SENDMONTH AS VARCHAR), 2) AS NVARCHAR) + '/' +CAST(RIGHT('00' +CAST(@SENDDAY AS VARCHAR), 2) AS NVARCHAR) + ' ' +CAST(RIGHT('00' +CAST(@SENDHOUR AS VARCHAR), 2) AS NVARCHAR) + ':' +CAST(RIGHT('00' +CAST(@SENDMINUTE AS VARCHAR), 2) AS NVARCHAR)SET @endTime = nullENDELSE IF (@STARTENDFLG = 1)BEGINSET @startTime = nullSET @endTime = CAST(RIGHT('0000' +CAST(@SENDYEAR AS VARCHAR), 4) AS NVARCHAR) + '/' +CAST(RIGHT('00' +CAST(@SENDMONTH AS VARCHAR), 2) AS NVARCHAR) + '/' +CAST(RIGHT('00' +CAST(@SENDDAY AS VARCHAR), 2) AS NVARCHAR) + ' ' +CAST(RIGHT('00' +CAST(@SENDHOUR AS VARCHAR), 2) AS NVARCHAR) + ':' +CAST(RIGHT('00' +CAST(@SENDMINUTE AS VARCHAR), 2) AS NVARCHAR)END--コイル数分繰り返すSET @i = 0WHILE (@i <= @PROCESSCOUNT)BEGIN  SET @orderNo = nullSET @coilNo = nullPRINT '----コイル数分 :' + CAST(@i AS nvarchar) + '-------PROCESSCOUNT :' +  CAST(@PROCESSCOUNT AS nvarchar)IF (@i = 1)BEGINSET @orderNo = @ORDERNO1SET @coilNo = @COILNO1ENDELSE IF (@i = 2)BEGINSET @orderNo = @ORDERNO2SET @coilNo = @COILNO2ENDELSE IF (@i = 3)BEGINSET @orderNo = @ORDERNO3SET @coilNo = @COILNO3ENDELSE IF (@i = 4)BEGINSET @orderNo = @ORDERNO4SET @coilNo = @COILNO4ENDELSE IF (@i = 5)BEGINSET @orderNo = @ORDERNO5SET @coilNo = @COILNO5ENDELSE IF (@i = 6)BEGINSET @orderNo = @ORDERNO6SET @coilNo = @COILNO6ENDELSE IF (@i = 7)BEGINSET @orderNo = @ORDERNO7SET @coilNo = @COILNO7ENDELSE IF (@i = 8)BEGINSET @orderNo = @ORDERNO8SET @coilNo = @COILNO8ENDELSE IF (@i = 9)BEGINSET @orderNo = @ORDERNO9SET @coilNo = @COILNO9ENDELSE IF (@i = 10)BEGINSET @orderNo = @ORDERNO10SET @coilNo = @COILNO10ENDELSE IF (@i = 11)BEGINSET @orderNo = @ORDERNO11SET @coilNo = @COILNO11ENDELSE IF (@i = 12)BEGINSET @orderNo = @ORDERNO12SET @coilNo = @COILNO12ENDELSE IF (@i = 13)BEGINSET @orderNo = @ORDERNO13SET @coilNo = @COILNO13ENDELSE IF (@i = 14)BEGINSET @orderNo = @ORDERNO14SET @coilNo = @COILNO14ENDELSE IF (@i = 15)BEGINSET @orderNo = @ORDERNO15SET @coilNo = @COILNO15ENDELSE IF (@i = 16)BEGINSET @orderNo = @ORDERNO16SET @coilNo = @COILNO16ENDELSE IF (@i = 17)BEGINSET @orderNo = @ORDERNO17SET @coilNo = @COILNO17ENDELSE IF (@i = 18)BEGINSET @orderNo = @ORDERNO18SET @coilNo = @COILNO18ENDELSE IF (@i = 19)BEGINSET @orderNo = @ORDERNO19SET @coilNo = @COILNO19ENDELSE IF (@i = 20)BEGINSET @orderNo = @ORDERNO20SET @coilNo = @COILNO20ENDELSE IF (@i = 21)BEGINSET @orderNo = @ORDERNO21SET @coilNo = @COILNO21ENDELSE IF (@i = 22)BEGINSET @orderNo = @ORDERNO22SET @coilNo = @COILNO22ENDELSE IF (@i = 23)BEGINSET @orderNo = @ORDERNO23SET @coilNo = @COILNO23ENDELSE IF (@i = 24)BEGINSET @orderNo = @ORDERNO24SET @coilNo = @COILNO24ENDELSE IF (@i = 25)BEGINSET @orderNo = @ORDERNO25SET @coilNo = @COILNO25ENDELSE IF (@i = 26)BEGINSET @orderNo = @ORDERNO26SET @coilNo = @COILNO26ENDELSE IF (@i = 27)BEGINSET @orderNo = @ORDERNO27SET @coilNo = @COILNO27ENDELSE IF (@i = 28)BEGINSET @orderNo = @ORDERNO28SET @coilNo = @COILNO28ENDELSE IF (@i = 29)BEGINSET @orderNo = @ORDERNO29SET @coilNo = @COILNO29ENDELSE IF (@i = 30)BEGINSET @orderNo = @ORDERNO30SET @coilNo = @COILNO30ENDELSE IF (@i = 31)BEGINSET @orderNo = @ORDERNO31SET @coilNo = @COILNO31ENDELSE IF (@i = 32)BEGINSET @orderNo = @ORDERNO32SET @coilNo = @COILNO32ENDELSE IF (@i = 33)BEGINSET @orderNo = @ORDERNO33SET @coilNo = @COILNO33ENDELSE IF (@i = 34)BEGINSET @orderNo = @ORDERNO34SET @coilNo = @COILNO34ENDELSE IF (@i = 35)BEGINSET @orderNo = @ORDERNO35SET @coilNo = @COILNO35ENDELSE IF (@i = 36)BEGINSET @orderNo = @ORDERNO36SET @coilNo = @COILNO36ENDELSE IF (@i = 37)BEGINSET @orderNo = @ORDERNO37SET @coilNo = @COILNO37ENDELSE IF (@i = 38)BEGINSET @orderNo = @ORDERNO38SET @coilNo = @COILNO38ENDELSE IF (@i = 39)BEGINSET @orderNo = @ORDERNO39SET @coilNo = @COILNO39ENDELSE IF (@i = 40)BEGINSET @orderNo = @ORDERNO40SET @coilNo = @COILNO40ENDELSE IF (@i = 41)BEGINSET @orderNo = @ORDERNO41SET @coilNo = @COILNO41ENDELSE IF (@i = 42)BEGINSET @orderNo = @ORDERNO42SET @coilNo = @COILNO42ENDELSE IF (@i = 43)BEGINSET @orderNo = @ORDERNO43SET @coilNo = @COILNO43ENDELSE IF (@i = 44)BEGINSET @orderNo = @ORDERNO44SET @coilNo = @COILNO44ENDELSE IF (@i = 45)BEGINSET @orderNo = @ORDERNO45SET @coilNo = @COILNO45ENDELSE IF (@i = 46)BEGINSET @orderNo = @ORDERNO46SET @coilNo = @COILNO46ENDELSE IF (@i = 47)BEGINSET @orderNo = @ORDERNO47SET @coilNo = @COILNO47ENDELSE IF (@i = 48)BEGINSET @orderNo = @ORDERNO48SET @coilNo = @COILNO48ENDELSE IF (@i = 49)BEGINSET @orderNo = @ORDERNO49SET @coilNo = @COILNO49ENDELSE IF (@i = 50)BEGINSET @orderNo = @ORDERNO50SET @coilNo = @COILNO50ENDELSE IF (@i = 51)BEGINSET @orderNo = @ORDERNO51SET @coilNo = @COILNO51ENDELSE IF (@i = 52)BEGINSET @orderNo = @ORDERNO52SET @coilNo = @COILNO52ENDELSE IF (@i = 53)BEGINSET @orderNo = @ORDERNO53SET @coilNo = @COILNO53ENDELSE IF (@i = 54)BEGINSET @orderNo = @ORDERNO54SET @coilNo = @COILNO54ENDELSE IF (@i = 55)BEGINSET @orderNo = @ORDERNO55SET @coilNo = @COILNO55ENDELSE IF (@i = 56)BEGINSET @orderNo = @ORDERNO56SET @coilNo = @COILNO56ENDELSE IF (@i = 57)BEGINSET @orderNo = @ORDERNO57SET @coilNo = @COILNO57ENDELSE IF (@i = 58)BEGINSET @orderNo = @ORDERNO58SET @coilNo = @COILNO58ENDELSE IF (@i = 59)BEGINSET @orderNo = @ORDERNO59SET @coilNo = @COILNO59ENDELSE IF (@i = 60)BEGINSET @orderNo = @ORDERNO60SET @coilNo = @COILNO60END       SET @i = @i + 1--ORDERNO, COILNO未入力時IF (@orderNo is null OR @coilNo is null)BEGINSET @message = 'オーダーNoまたはコイルNoがNULL オーダーNo:' + @orderNo + ' コイルNo:' + @coilNoPRINT(@message)CONTINUEEND--コイルテーブルをチェックSELECT @serialNo = SERIALCODE FROM T_COILWHEREORDERNO = @orderNoAND COILNO = @coilNoAND HISTORYFLG = 0AND (DELETEFLG IS NULL OR DELETEFLG = 0)IF @serialNo IS NULLBEGINSET @message = 'T_COILに該当なし オーダーNo:' + @orderNo + ' コイルNo:' + @coilNoPRINT(@message)CONTINUEEND--コイル実績情報表示テーブルの既存レコードを確認SELECT @count = COUNT(*) FROM T_COILINFOWHERESERIALNO = @serialNoAND EQUIPMENTCODE = @EQUIPMENTCODEPRINT '-----コイル数分++ :' + CAST(@i AS nvarchar)-----'PRINT 'コイルカウント数:'+CAST(@coilCount AS nvarchar)PRINT 'コイル数分 :' + CAST(@i AS nvarchar)PRINT 'PROCESSCOUNT :' +  CAST(@PROCESSCOUNT AS nvarchar)PRINT 'ORDERNO  : ' + @orderNoPRINT 'COILNO   : ' + @coilNoPRINT 'EQCODE   : ' + @EQUIPMENTCODEPRINT 'EQDETAIL1: ' + @EQUIPMENTDETAILCODE1PRINT 'コイル実績情報表示テーブル(T_COILINFO)の既存レコードを確認'PRINT 'SERIALNO :'+ @serialNoPRINT 'COUNT    :' + CAST(@count AS nvarchar)--コイル実績情報表示テーブルに追加または更新IF @count = 0BEGIN--新規INSERT INTO T_COILINFO VALUES (@serialNo, @EQUIPMENTCODE, @EQUIPMENTDETAILCODE1, @EQUIPMENTDETAILCODE2, @startTime, @endTime, @WORKERCODE1, @WORKERCODE2, @WORKERCODE3, @WORKERCODE4, @WORKERCODE5, @ITEMNO1, @VALUE1, @ITEMNO2, @VALUE2, @ITEMNO3, @VALUE3, @ITEMNO4, @VALUE4, @ITEMNO5, @VALUE5, @ITEMNO6, @VALUE6, @ITEMNO7, @VALUE7, @ITEMNO8, @VALUE8, @ITEMNO9, @VALUE9, @ITEMNO10, @VALUE10, @ITEMNO11, @VALUE11, @ITEMNO12, @VALUE12, @ITEMNO13, @VALUE13, @ITEMNO14, @VALUE14, @ITEMNO15, @VALUE15, @ITEMNO16, @VALUE16, @ITEMNO17, @VALUE17, @ITEMNO18, @VALUE18, @ITEMNO19, @VALUE19, @ITEMNO20, @VALUE20, GETDATE(), GETDATE(), 'T_STARTENDDATA', 0)  PRINT 'INSERT  T_COILINFO'ENDELSEBEGIN--更新IF (@STARTENDFLG = 0)/*開始*/BEGINUPDATE T_COILINFO SET  EQUIPMENTCODE = @EQUIPMENTCODE  , EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1  , EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2  , STARTTIME = @startTime  , ENDTIME = null  , WORKERCODE1 = @WORKERCODE1  , WORKERCODE2 = @WORKERCODE2  , WORKERCODE3 = @WORKERCODE3  , WORKERCODE4 = @WORKERCODE4  , WORKERCODE5 = @WORKERCODE5  , ITEMNO1 = @ITEMNO1, VALUE1 = @VALUE1  , ITEMNO2 = @ITEMNO2, VALUE2 = @VALUE2  , ITEMNO3 = @ITEMNO3, VALUE3 = @VALUE3  , ITEMNO4 = @ITEMNO4, VALUE4 = @VALUE4  , ITEMNO5 = @ITEMNO5, VALUE5 = @VALUE5  , ITEMNO6 = @ITEMNO6, VALUE6 = @VALUE6  , ITEMNO7 = @ITEMNO7, VALUE7 = @VALUE7  , ITEMNO8 = @ITEMNO8, VALUE8 = @VALUE8  , ITEMNO9 = @ITEMNO9, VALUE9 = @VALUE9  , ITEMNO10 = @ITEMNO10, VALUE10 = @VALUE10  , ITEMNO11 = @ITEMNO11, VALUE11 = @VALUE11  , ITEMNO12 = @ITEMNO12, VALUE12 = @VALUE12  , ITEMNO13 = @ITEMNO13, VALUE13 = @VALUE13  , ITEMNO14 = @ITEMNO14, VALUE14 = @VALUE14  , ITEMNO15 = @ITEMNO15, VALUE15 = @VALUE15  , ITEMNO16 = @ITEMNO16, VALUE16 = @VALUE16  , ITEMNO17 = @ITEMNO17, VALUE17 = @VALUE17  , ITEMNO18 = @ITEMNO18, VALUE18 = @VALUE18  , ITEMNO19 = @ITEMNO19, VALUE19 = @VALUE19  , ITEMNO20 = @ITEMNO20, VALUE20 = @VALUE20  , UPDATETIME = GETDATE()  , UPUSER = 'T_STARTENDDATA'  , DELETEFLG = 0WHERE  SERIALNO = @serialNo  AND EQUIPMENTCODE = @EQUIPMENTCODE  --PRINT 'UPDATE T_COILINFO SERIALNO='+@serialNo +' EQUIPMENTCODE=' + @EQUIPMENTCODE  --PRINT '  EQUIPMENTCODE ='''+ @EQUIPMENTCODE+''      PRINT 'UPDATE T_COILINFO 'ENDELSE IF (@STARTENDFLG = 1)/*終了*/print '@STARTENDFLG = '+ @STARTENDFLGBEGINUPDATE T_COILINFO SET  EQUIPMENTCODE = @EQUIPMENTCODE  , EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1  , EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2  , ENDTIME = @endTime  , WORKERCODE1 = @WORKERCODE1  , WORKERCODE2 = @WORKERCODE2  , WORKERCODE3 = @WORKERCODE3  , WORKERCODE4 = @WORKERCODE4  , WORKERCODE5 = @WORKERCODE5  , ITEMNO1 = @ITEMNO1, VALUE1 = @VALUE1  , ITEMNO2 = @ITEMNO2, VALUE2 = @VALUE2  , ITEMNO3 = @ITEMNO3, VALUE3 = @VALUE3  , ITEMNO4 = @ITEMNO4, VALUE4 = @VALUE4  , ITEMNO5 = @ITEMNO5, VALUE5 = @VALUE5  , ITEMNO6 = @ITEMNO6, VALUE6 = @VALUE6  , ITEMNO7 = @ITEMNO7, VALUE7 = @VALUE7  , ITEMNO8 = @ITEMNO8, VALUE8 = @VALUE8  , ITEMNO9 = @ITEMNO9, VALUE9 = @VALUE9  , ITEMNO10 = @ITEMNO10, VALUE10 = @VALUE10  , ITEMNO11 = @ITEMNO11, VALUE11 = @VALUE11  , ITEMNO12 = @ITEMNO12, VALUE12 = @VALUE12  , ITEMNO13 = @ITEMNO13, VALUE13 = @VALUE13  , ITEMNO14 = @ITEMNO14, VALUE14 = @VALUE14  , ITEMNO15 = @ITEMNO15, VALUE15 = @VALUE15  , ITEMNO16 = @ITEMNO16, VALUE16 = @VALUE16  , ITEMNO17 = @ITEMNO17, VALUE17 = @VALUE17  , ITEMNO18 = @ITEMNO18, VALUE18 = @VALUE18  , ITEMNO19 = @ITEMNO19, VALUE19 = @VALUE19  , ITEMNO20 = @ITEMNO20, VALUE20 = @VALUE20  , UPDATETIME = GETDATE()  , UPUSER = 'T_STARTENDDATA'  , DELETEFLG = 0WHERE  SERIALNO = @serialNo  AND EQUIPMENTCODE = @EQUIPMENTCODE END  PRINT 'UPDATE T_COILINFO222222 'END--コイル状態テーブルの既存レコードを確認SELECT @count = COUNT(*) FROM T_COILTRACKINGWHERE SERIALNO = @serialNo--コイル状態テーブルに追加または更新DECLARE @coilStatusId intIF (@STARTENDFLG = 1 AND @EQUIPMENTCODE = 'A050'/*仕上げ乾燥*/)BEGINSET @coilStatusId = 6/*仕上げ完了*/ENDELSEBEGINSET @coilStatusId = 2/*投入中*/ENDIF @count = 0BEGIN--新規INSERT INTO T_COILTRACKING VALUES (@serialNo, @EQUIPMENTCODE, @EQUIPMENTDETAILCODE1, @EQUIPMENTDETAILCODE2, @coilStatusId, NULL, 0, GETDATE(), GETDATE(), 'T_STARTENDDATA', 0)  PRINT 'INSERT INTO T_COILTRACKING 'ENDELSEBEGIN--更新UPDATE T_COILTRACKING SETEQUIPMENTCODE = @EQUIPMENTCODE, EQUIPMENTDETAILCODE1 = @EQUIPMENTDETAILCODE1, EQUIPMENTDETAILCODE2 = @EQUIPMENTDETAILCODE2, COILSTATUSID = @coilStatusId, UPDATETIME = GETDATE(), UPUSER = 'T_STARTENDDATA', DELETEFLG = 0WHERESERIALNO = @serialNoEND  PRINT 'UPDATE T_COILTRACKING 'ENDCLOSE StartEndCurDEALLOCATE StartEndCurCOMMIT TRANSACTIONEND TRYBEGIN CATCHDEALLOCATE StartEndCur  PRINT 'ROLLBACK TRANSACTION 'ROLLBACK TRANSACTIONPRINT 'エラーが発生しました： ' + CAST(ERROR_MESSAGE() AS nvarchar ) + ' L:' + CAST(ERROR_LINE() AS nvarchar)END CATCH