declare @Count int, @intFlag int
set @Count = (select count(ID) from MyTable where [Date] between getdate()-1 and getdate())
if @Count > 0
begin
set @intFlag = 1
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;
end
This is obviously a mock query but the basic layout is the same as one I have in production. Where should BEGIN, COMMIT, ROLLBACK transaction be placed so that if an error is encountered all work from previous iterations is rolled back? I am assuming the transaction should start outside of the loop but would just like clarification on the best way this should be approached.
Thanks.
Transaction Handling in While Loop
Posted - 03/29/2012 : 04:56:11
