Nils Gustav Stråbø (11/13/2012)
Thanks for the question.Got it wrong because I missed the last INSERT statement after the WHILE LOOP.
If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.
A much more readable example that also tests the users knowledge of transactions could have been
create table #t1(id int)insert into #t1 values(1)
begin tran t1
insert into #t1 values(2)
begin tran t2
insert into #t1 values(3)
begin tran t3
insert into #t1 values(3)
if @@trancount>0
rollback tran
if @@trancount>0
commit tran t2
if @@trancount>0
rollback tran t1
select * from #t1
the point here is.. once you check the tran count and you roll back them - it rolls back all of them... so there is no point to check the transaction again and commit it, as it never going to be greater than 0 after rolled back, in your example you created the transaction each one manually and the same is achieved via while loop in the Qtod, and there were some statements which adds kind of interest to the analysis where the making a choice going to take a while.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.