以下这段,不知道出了什么问题,资料量也不大,只有251笔,但是,执行的时候就好像陷入了死循环一样不停的执行,执行很久,但是不出错!!!请各位帮忙看下!辛苦了~~~DECLARE @REF_MS CHAR(20) --料號1
DECLARE @REF_M1 CHAR(20) --料號2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DOOPEN cursor1 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1WHILE @@FETCH_STATUS = 0 --0 FETCH 語句成功BEGINUPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
SET tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
WHERE tc_imh014 IN ('I') -- 新增
AND tc_imh050 = 'C00'
AND tc_imh006 > 0
AND tc_imh012 = @REF_MS --料號1
AND tc_imh013 = @REF_M1 --料號2 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1ENDCLOSE cursor1 --關閉游標DEALLOCATE cursor1 --釋放游標RETURNEND
DECLARE @REF_M1 CHAR(20) --料號2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DOOPEN cursor1 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1WHILE @@FETCH_STATUS = 0 --0 FETCH 語句成功BEGINUPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
SET tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
WHERE tc_imh014 IN ('I') -- 新增
AND tc_imh050 = 'C00'
AND tc_imh006 > 0
AND tc_imh012 = @REF_MS --料號1
AND tc_imh013 = @REF_M1 --料號2 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1ENDCLOSE cursor1 --關閉游標DEALLOCATE cursor1 --釋放游標RETURNEND
tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
FROM OPENQUERY(ERP,'SELECT * FROM tc_imh_file') AS A
JOIN SC_DO AS B
ON A.tc_imh012=B.REF_MS
AND A.tc_imh013=B.REF_M1
WHERE A.tc_imh014 IN ('I') -- 新增
AND A.tc_imh050 = 'C00'
AND A.tc_imh006 > 0
-- DO(INSERT, UPDATE)--STEP01
--ERP資料插入S/C
--select * from SC.dbo.sc_do
BEGIN TRY
BEGIN TRAN
INSERT INTO SC.dbo.SC_DO
SELECT tc_imh001,tc_imh002,tc_imh003,tc_imh004,tc_imh005,
tc_imh006,tc_imh007,tc_imh008,tc_imh009,tc_imh010,
tc_imh011,tc_imh012,tc_imh013,tc_imh045,tc_imh045,
tc_imh046
FROM OPENQUERY(ERP,'select * FROM tc_imh_file')
WHERE tc_imh014 IN ('I') -- 新增
AND tc_imh050 = 'C00'
COMMIT TRAN
END TRYBEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRAN
END CATCH其實,我的目的是想說當上面執行catch到錯誤後,那嚜就不執行以下這段了!
所以,我有判斷兩個標裡面的資料相同才跟新,或者大家有沒有別的方法來寫.
--STEP02
--S/C UPDATE ERP STATUS CODE
DECLARE @REF_MS CHAR(20) --料號1
DECLARE @REF_M1 CHAR(20) --料號2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DOOPEN cursor1 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1WHILE @@FETCH_STATUS = 0 --0 FETCH 語句成功BEGIN--UPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
-- SET tc_imh048 = 'A.E2S',
-- tc_imh050 = 'C01'
-- WHERE tc_imh014 IN ('I') -- 新增
-- AND tc_imh050 = 'C00'
-- AND tc_imh006 > 0
-- AND tc_imh012 = @REF_MS
-- AND tc_imh013 = @REF_M1UPDATE A SET
tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
FROM OPENQUERY(ERP,'SELECT * FROM tc_imh_file') AS A
JOIN SC_DO AS B
ON A.tc_imh012=B.REF_MS
AND A.tc_imh013=B.REF_M1
--WHERE A.tc_imh014 IN ('I') -- 新增
-- AND A.tc_imh050 = 'C00'
-- AND A.tc_imh006 > 0
where A.tc_imh001 = 'RDT10911170047901' FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1ENDCLOSE cursor1 --關閉游標DEALLOCATE cursor1 --釋放游標RETURNEND
end try 如果检测到错误,会自动执行end catch 后面的语句。
begin catch
end catch 要想出错后,不执行下面的语句。就在end catch 后加个判断条件吧
所以,我跟新语句到底哪里写错了呢。DECLARE @REF_MS CHAR(20) --料號1
DECLARE @REF_M1 CHAR(20) --料號2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DOOPEN cursor1 FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1
print 1
WHILE @@FETCH_STATUS = 0 --0 FETCH 語句成功BEGIN--UPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
-- SET tc_imh048 = 'A.E2S',
-- tc_imh050 = 'C01'
-- WHERE tc_imh014 IN ('I') -- 新增
-- AND tc_imh050 = 'C00'
-- AND tc_imh006 > 0
-- AND tc_imh012 = @REF_MS
-- AND tc_imh013 = @REF_M1UPDATE A SET
tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
FROM OPENQUERY(ERP,'SELECT * FROM tc_imh_file') AS A
JOIN SC_DO AS B
ON A.tc_imh012=B.REF_MS
AND A.tc_imh013=B.REF_M1
--WHERE A.tc_imh014 IN ('I') -- 新增
-- AND A.tc_imh050 = 'C00'
-- AND A.tc_imh006 > 0
where A.tc_imh001 = 'RDT10911170047901' FETCH NEXT FROM cursor1 INTO @REF_MS,@REF_M1
print 2
ENDCLOSE cursor1 --關閉游標DEALLOCATE cursor1 --釋放游標RETURN
print 3