none
Where is the good place to place a Rollback RRS feed

  • Question

  • Dear all, I am running the following querry :

    SET @results = 1
    SET @batchSize = 100
    SET @id_control = 0
    
    BEGIN TRY    
      WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		
    	  UPDATE TOP (100) myTable SET myField = "aaa"	  
    		 
    		  SET @results = @@ROWCOUNT;
    		  IF @@ROWCOUNT < @BatchSize
    			BEGIN
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    	     COMMIT TRANSACTION   
    	
      END;
    END TRY
    BEGIN CATCH
      RAISERROR('fail to update record :',1,1,'');
      RETURN;
    END CATCH;

    IN case my update querry fails in the middle of a batch set, I need to rollback where can I place my rolback, right after my RAISEERROR in the try catch block ?

    Thanks for advise

    Tuesday, September 17, 2019 12:54 PM

All replies

  • right after my RAISEERROR in the try catch block ?

    In the catch block, but directly before the RAISERROR

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 17, 2019 12:59 PM
  • You do not need an explicit transaction around a single UPDATE statement.  Every statement is an implicit transaction.

    If there is more to it than you posted, then you should do it like this:

    SET @results = 1;
    SET @batchSize = 100;
    SET @id_control = 0;
    
    BEGIN TRY
        WHILE(@results > 0)
    	   BEGIN
    		  BEGIN TRANSACTION;
    
    		  UPDATE TOP (100) [myTable] SET [myField] = [aaa];
    
    		  SET @results = @@ROWCOUNT;
    
    		  COMMIT TRANSACTION;
    		  IF @results < @BatchSize
    			 BEGIN
    				BREAK;
    		  END;
    	   END;
    END TRY
    BEGIN CATCH
        RAISERROR('fail to update record :', 1, 1, '');
        IF @@trancount > 0 ROLLBACK;
        RETURN;
    END CATCH;


    Tuesday, September 17, 2019 1:12 PM
    Moderator
  • A CATCH block should always start with

      IF @@trancount > 0 ROLLBACK TRANSACTION

    You still have this part wrong:

                SET @results = @@ROWCOUNT;
                IF @@ROWCOUNT < @BatchSize

    @@rowcount returns the number of rows affected by the most recent statement. Which is the second case is the SET statement, so @@rowcount will always be 1 here.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, September 17, 2019 9:59 PM