CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user3844864
user3844864

Reputation: 57

error handling inside a while loop

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

Upvotes: 2

Views: 2817

Answers (1)

Mubashar
Mubashar

Reputation: 12668

try catch block above the loop like following 

BEGIN TRANSACTION 

BEGIN TRY

/*  
 *  YOUR LOOP
 */

COMMIT TRANSACTION 
END TRY
BEGIN CATCH 

ROLLBACK TRANSACTION

END CATCH

Upvotes: 3

Related Questions