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

  • DateDiff to output hours and minutes
  • How to use Substring in SSIS
  • SQL Server FOR XML PATH ROOT element with attribute (Path Mode symbol @ or / )
  • Create Distinct Column Values as Extra Rows
  • Optimize queries
  • SQL - how do I generate rows for each month based on date ranges in existing dataset?
  • 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
              else
                      Call Add store proc
    
              Commit transaction
              End try
              Begin Catch
                 if @@Trancount > 0 ROLLBACK TRANSACTION
              END CATCH
     FETCH NEXT
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.