error handling inside a while loop

Question

I have 8 tables . One parent and 7 children . Inside the while loop and delete from table one by one. If any error during the loop all transaction rollback . Is it possible inside the while loop.

Example :

declare @Count int, @intFlag int

begin try set @Count = (select count(ID) from MyTable where [Date] between getdate()-1 and getdate()) if @Count > 0

begin set @intFlag = 1

begin transaction

    while (@intFlag <= @Count)
begin

    update MyTable1
        set column1 = 1
    where [Date] between getdate()-1 and getdate();

    update MyTable2
        set column2 = 1
    where [Date] between getdate()-1 and getdate();

set @intFlag = @intFlag + 1
end;

commit

end

end try

begin catch if @@trancount > 0 rollback end catch

If any error during the processes its roll back all child table transaction

Start a transaction before the loop. If you can use try-catch to detect error, have the loop inside a try-catch block with commit transaction at the end of try block and rollback inside catch block — Sach

Mubashar Mubashar · Accepted Answer · 2014-12-10T05:03:43

try catch block above the loop like following

BEGIN TRANSACTION 

BEGIN TRY

/*  
 *  YOUR LOOP
 */

COMMIT TRANSACTION 
END TRY
BEGIN CATCH 

ROLLBACK TRANSACTION

END CATCH

error handling inside a while loop

1 Answers