CODEWITHSUNDEEP
sql-serversql-server-2008
jvm
jvm

Reputation: 1712

Error Handling with Cursor in SQL

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.

Any help? I am using SQL Server 2008

Upvotes: 2

Views: 13753

Answers (1)

jvm
jvm

Reputation: 1712

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

Upvotes: 5

Related Questions