Transaction Handling in While Loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction Handling in While Loop

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2012-03-29 : 04:56:11

[code]
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
[/code]

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.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-03-29 : 05:10:19

[code]
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

[/code]

patshaw
Posting Yak Master

177 Posts

Posted - 2012-03-29 : 06:05:00

Thanks Rick, I'll give it a go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources