SQL中使用Cursor进行错误处理

I have cursor which either Update table or Add data if record don't exist. My requirement is

我有光标,如果记录不存在,则更新表或添加数据。我的要求是

OPEN CURSOR
     Fetch Next
        While @@fetch_status = 0
          if (Record found) then 
              call update stored procedure
          else
              call Add stored procedure

Now, issue I am having is, both update/add stored procedure call multiple other stored procedures to do operation. If anything wrong with any other stored procedure, I need to rollback everything

现在,我遇到的问题是,更新/添加存储过程调用多个其他存储过程来做操作。如果任何其他存储过程有任何问题,我需要回滚所有内容

I did try with Begin Transaction and checking if @@Error but it didn't work.

我尝试使用Begin Transaction并检查是否@@ Error但是它不起作用。

Any help? I am using SQL Server 2008

有帮助吗?我正在使用SQL Server 2008

1 个解决方案

#1

Open Cursor
 Fetch Next
        While @@fetch_status = 0
          BEGIN TRY
            BEGIN TRANSACTION
          if (Record found) then Call update Store proc
          else
                  Call Add store proc

          Commit transaction
          End try
          Begin Catch
             if @@Trancount > 0 ROLLBACK TRANSACTION
          END CATCH
 FETCH NEXT