USE SC GO BEGIN
-- 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 TRY BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRAN
END CATCH 其实,我的目的是想说当上面执行catch到错误后,那嚜就不执行以下这段了!
所以,我有判断两个标里面的资料相同才跟新,或者大家有没有别的方法来写.
我刚才把语句中加入了PRINT以后,发现,PRINT了一次1之后,就一直不停PRINT2,根本就没有PRINT 3.
所以,我跟新语句到底哪里写错了呢。 DECLARE @REF_MS CHAR(20) --料号1
DECLARE @REF_M1 CHAR(20) --料号2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DO OPEN 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_M1 UPDATE 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
END CLOSE cursor1 --关闭游标 DEALLOCATE cursor1 --释放游标 RETURN
print 3END
-- 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 TRY BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRAN
END CATCH 其实,我的目的是想说当上面执行catch到错误后,那嚜就不执行以下这段了!
所以,我有判断两个标里面的资料相同才跟新,或者大家有没有别的方法来写.
我刚才把语句中加入了PRINT以后,发现,PRINT了一次1之后,就一直不停PRINT2,根本就没有PRINT 3.
所以,我跟新语句到底哪里写错了呢。 DECLARE @REF_MS CHAR(20) --料号1
DECLARE @REF_M1 CHAR(20) --料号2
DECLARE cursor1 CURSOR FOR
SELECT REF_MS,REF_M1 FROM SC_DO OPEN 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_M1 UPDATE 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
END CLOSE cursor1 --关闭游标 DEALLOCATE cursor1 --释放游标 RETURN
print 3END
看看这个是什么结果
SELECT REF_MS,REF_M1 FROM SC_DO
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' 看你这个更新语句也没用到游标用到的2个变量么?
@REF_MS,@REF_M1
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_imh001=B.DO_CODE
-- 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'
SELECT DO_CODE FROM SC_DOOPEN cursor1 FETCH NEXT FROM cursor1 INTO @DO_CODEWHILE @@FETCH_STATUS = 0 --0 FETCH 語句成功BEGINUPDATE 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_imh001=B.DO_CODE
where A.tc_imh001 = @DO_CODE FETCH NEXT FROM cursor1 INTO @DO_CODEENDCLOSE cursor1 --關閉游標DEALLOCATE cursor1 --釋放游標RETURN
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_imh001=B.DO_CODE 这个就不要连接了
where A.tc_imh001 = @DO_CODE
还是一直在update这里循环!
就算我把WHERE 里面的变量换成是一个已存在表里的常数,他还是照样的重复执行UPDATE!
奇怪!
'RDT10911170047901'
但是,我不写成是带游标的方式,直接UPDATE就只有10几秒就可以了。不知道问题出在哪里,所以,可以麻烦您问下,如果我在之前的TRY CATCH 结束后,如果有CATCH到错误后,后面的update 就直接不执行了,直接结束,该怎么写?
if @ERROR_MESSAGE() is not null
begin
ROLLBACK TRAN
end
else
begin
--这里写你下面的代码
end
end catch
这样写的话会出以下错误
訊息 137,層級 15,狀態 2,行 29
必須宣告純量變數 "@ERROR_MESSAGE"。我写成这样,可以吗?
BEGIN CATCH
--SELECT ERROR_MESSAGE() AS ErrorMessage
IF ERROR_MESSAGE() is not null
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
UPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
SET tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
WHERE tc_imh014 IN ('I') -- 新增
AND tc_imh001 = 'RDT10911170047901'
END
END CATCHEND
USE SCGOBEGIN
-- DO(INSERT, UPDATE)--STEP01
--ERP資料插入S/C
--select * from SC.dbo.sc_do
SELECT ERROR_MESSAGE() AS ErrorMessage
BEGIN TRY
BEGIN TRAN
INSERT INTO SC.dbo.SC_DO
SELECT tc_imh001,' ',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 TRY--BEGIN CATCH
-- SELECT ERROR_MESSAGE() AS ErrorMessage
-- ROLLBACK TRAN
--
--END CATCHBEGIN CATCH
--SELECT ERROR_MESSAGE() AS ErrorMessage
IF ERROR_MESSAGE() is not null
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
UPDATE OPENQUERY (ERP, 'SELECT * FROM tc_imh_file')
SET tc_imh048 = 'A.E2S',
tc_imh050 = 'C01'
WHERE tc_imh014 IN ('I') -- 新增
AND tc_imh001 = 'RDT10911170047901'
END
END CATCHEND