Error Handling with Cursor in SQL

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

     Fetch Next
        While @@fetch_status = 0
          if (Record found) then 
              call update stored procedure
              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

  • Does sql server trigger has order of execution?
  • sql server 2008 Login failed for user 'NT AUTHORITY\NETWORK SERVICE'
  • different estimated rows on same index operation?
  • SQL Server 2008 Auto Backup
  • Fetch records from Database in Tree Structure
  • How to pass XML from C# to a stored procedure in SQL Server 2008?
  • I did try with Begin Transaction and checking if @@Error but it didn’t work.

    Any help? I am using SQL Server 2008

    One Solution collect form web for “Error Handling with Cursor in SQL”

    Open Cursor
     Fetch Next
            While @@fetch_status = 0
              BEGIN TRY
                BEGIN TRANSACTION
              if (Record found) then Call update Store proc
                      Call Add store proc
              Commit transaction
              End try
              Begin Catch
                 if @@Trancount > 0 ROLLBACK TRANSACTION
              END CATCH
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.