Problem with transaction inside 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 2005 Forums

Transact-SQL (2005)

Problem with transaction inside loop

Author

Topic

Manivannan.kariamal
Starting Member

9 Posts

Posted - 2008-05-06 : 06:29:05

Hi,

When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.

Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest

Please let me know the reason for this.

Thanks in advance

Regards,
K. Manivannan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-06 : 12:46:28

You are not rolling back or commiting the first 5 transactions you opened. Try this:

CREATE TABLE #BPTest (id int)
Declare @Id Int

Set @Id = 0

While (@Id < 10)
Begin
	begin tran
	
	Insert into #BPTest values (@id)
	
	if(@Id > 5)
	begin
		if(@Id % 2 = 0)
		begin
			print 'true' print @Id
			commit tran
		end
		else
		begin
			print 'false' print @Id
			rollback tran
		end
	end
	ELSE
	BEGIN
		commit tran
	END
	Set @Id = @Id + 1
End
Select * from #BPTest 

DROP TABLE #BPTest

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources