帮忙看看这个游标,有一条记录出错了,后面就不执行了,希望如果出错就跳过去,接着执行下一条:
--/**
* 定义行ID和识别符
*/
DECLARE @sod_id nvarchar(64), @so_class nvarchar(16), @doFlag1 INT
DECLARE @so_code nvarchar(64), @newso_code nvarchar(64)
/**
* 定义游标 遍历明细ID
*/
DECLARE cur CURSOR FOR
SELECT sod_id, so_class, doFlag1, so_code
FROM crm_sod
WHERE doFlag1 = 1--打开游标
OPEN cur--指向第一行 并将第一行的ID和识别符赋给变量
FETCH NEXT FROM cur
INTO @sod_id, @so_class, @doFlag1,@so_code--遍历游标
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@so_class = 3)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上退货标志
SET @newso_code = 'T'+@so_code
-- 检查退货主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_back WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员退货单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在退货请表中创建退货主键
INSERT INTO crm_som_back
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为退货外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END
ELSE
IF (@so_class = 30)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上换货标志
SET @newso_code = 'H'+@so_code
-- 检查换货主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_change WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员换货单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在换货主表中创建换货主键
INSERT INTO crm_som_change
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为换货外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END
ELSE
IF (@so_class = 1)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上销售标志
SET @newso_code = 'S'+@so_code
-- 检查销售主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_sale WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员销售订单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在销售主表中创建销售主键
INSERT INTO crm_som_sale
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为销售外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END--游标指向下一行
FETCH NEXT FROM cur
INTO @sod_id, @so_class, @doFlag1,@so_code
END
--关闭游标
CLOSE cur
--释放游标
DEALLOCATE cur
--/**
* 定义行ID和识别符
*/
DECLARE @sod_id nvarchar(64), @so_class nvarchar(16), @doFlag1 INT
DECLARE @so_code nvarchar(64), @newso_code nvarchar(64)
/**
* 定义游标 遍历明细ID
*/
DECLARE cur CURSOR FOR
SELECT sod_id, so_class, doFlag1, so_code
FROM crm_sod
WHERE doFlag1 = 1--打开游标
OPEN cur--指向第一行 并将第一行的ID和识别符赋给变量
FETCH NEXT FROM cur
INTO @sod_id, @so_class, @doFlag1,@so_code--遍历游标
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@so_class = 3)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上退货标志
SET @newso_code = 'T'+@so_code
-- 检查退货主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_back WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员退货单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在退货请表中创建退货主键
INSERT INTO crm_som_back
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为退货外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END
ELSE
IF (@so_class = 30)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上换货标志
SET @newso_code = 'H'+@so_code
-- 检查换货主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_change WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员换货单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在换货主表中创建换货主键
INSERT INTO crm_som_change
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为换货外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END
ELSE
IF (@so_class = 1)
BEGIN
-- 获得明细的主表外键
SET @sod_id = (SELECT TOP 1 sod_id FROM crm_sod WHERE sod_id = @sod_id)
-- 外键加上销售标志
SET @newso_code = 'S'+@so_code
-- 检查销售主表中该外键是否已存在
IF NOT exists(SELECT * FROM crm_som_sale WHERE so_code = @newso_code)
BEGIN
update crm_som set so_class = '会员销售订单'
from crm_som
where crm_som.so_code = @so_code
--如果不存在,在销售主表中创建销售主键
INSERT INTO crm_som_sale
(doflag,err_msg,so_class,so_code,is_locked,friend_code,emp_id,so_date,so_amount,intro_name,intro_id,so_memo1k,so_buyreson1,so_lasted,so_branch)
SELECT
crm_som.doflag,crm_som.err_msg,crm_som.so_class,@newso_code,crm_som.is_locked,crm_som.friend_code,crm_som.emp_id,crm_som.so_date,crm_som.so_amount,crm_som.intro_name,crm_som.intro_id,crm_som.so_memo1k,crm_som.so_buyreson1,crm_som.so_lasted,crm_som.so_branch
FROM crm_som
WHERE crm_som.so_code= @so_code
END
--将该明细的主表外键改为销售外键
UPDATE crm_sod SET crm_sod.so_code = @newso_code ,doFlag1 = 0
FROM crm_sod
WHERE crm_sod.sod_id = @sod_id
END--游标指向下一行
FETCH NEXT FROM cur
INTO @sod_id, @so_class, @doFlag1,@so_code
END
--关闭游标
CLOSE cur
--释放游标
DEALLOCATE cur
begin tran
update..... --游标内部的操作功能代码
commit
end try
begin catch
rollback
end catch
附上说明SET XACT_ABORT ON/OFF
指定當 Transact-SQL 陳述式發生執行時期錯誤時,Microsoft® SQL Server™ 是否會自動復原目前的交易。
語法
SET XACT_ABORT { ON | OFF }
備註
當 SET XACT_ABORT 設定為 ON 時,如果 Transact-SQL 陳述式發生執行期錯誤,整個交易都會中斷並復原。設定為 OFF 時,只有發生錯誤的 Transact-SQL 陳述式會復原,交易將繼續處理。編譯錯誤,例如語法錯誤並不會受到 SET XACT_ABORT 的影響。
大部份 OLE DB 提供者的隱含或內顯交易的資料修改陳述式都要求必須將 XACT_ABORT 設定為 ON,包含 SQL Server在內。唯一不需要設定此選項的情形是當提供者支援巢狀交易時。如需詳細資訊,請參閱分散式查詢及分散式交易。
SET XACT_ABORT 的設定是在執行或執行時期設定,而不是在剖析時期設定