sql server - While-clause in T-SQL that loops forever -

i tasked debugging strange problem within e-commerce application. after application upgrade site started hang time time , sent in debug. after checking event log found sql-server wrote ~200 000 events in couple of minutes message saying constraint had failed. after debugging , tracing found culprit. i've removed unnecessary code , cleaned bit it

while exists (select * shoppingcartitem shoppingcartitem.purchid = @purchid) begin     select top 1          @tmpgfsid = shoppingcartitem.gfsid,          @tmpquantity = shoppingcartitem.quantity,         @tmpshoppingcartitemid = shoppingcartitem.shoppingcartitemid,             shoppingcartitem inner join goodsforsale on shoppingcartitem.gfsid = goodsforsale.gfsid     shoppingcartitem.purchid = @purchid      exec @errorcode = spgoodsforsale_reversereservations @tmpgfsid, @tmpquantity     if @errorcode <> 0     begin         goto cleanup         end      delete shoppingcartitem shoppingcartitem.shoppingcartitemid = @tmpshoppingcartitemid     -- @@rowcount 1 after end 

facts:

1. there's 1 or 2 records matching first select-clause
2. rowcount delete statement indicates has been removed
3. the while-clause loop forever

the procedure has been rewritten select rows should deleted temporary in-memory table instead immediate problem solved sparked curiosity.

why loop forever?

clarification: delete doesn't fail (@@rowcount 1 after delete stmt when debugged) clarification 2: shouldn't matter whether or not select top ... clause ordered specific field since record returned id deleted in next loop should record.

update: after checking subversion logs found culprit commit made stored procedure go haywire. real difference can find there no join in select top 1 statement i.e. without join worked without transaction statements surrounding delete. appears introduction of join made sql server more picky.

update clarification: brien pointed out there's no need join use fields goodsforsale table i've removed them keep code can concentrate on problem @ hand

are operating in explicit or implicit transaction mode?

since you're in explicit mode, think need surround delete operation begin transaction , commit transaction statements.

while exists (select * shoppingcartitem shoppingcartitem.purchid = @purchid) begin     select top 1              @tmpgfsid = shoppingcartitem.gfsid,              @tmpquantity = shoppingcartitem.quantity,             @tmpshoppingcartitemid = shoppingcartitem.shoppingcartitemid,                 shoppingcartitem inner join goodsforsale on shoppingcartitem.gfsid = goodsforsale.gfsid     shoppingcartitem.purchid = @purchid      exec @errorcode = spgoodsforsale_reversereservations @tmpgfsid, @tmpquantity     if @errorcode <> 0     begin             goto cleanup         end      begin transaction delete          delete shoppingcartitem shoppingcartitem.shoppingcartitemid = @tmpshoppingcartitemid         -- @@rowcount 1 after      commit transaction delete end 

clarification: reason you'd need use transactions delete doesn't happen in database until commit operation. used when have multiple write operations in atomic transaction. basically, want changes happen db if of operations successful.

in case, there's 1 operation, since you're in explicit transaction mode, need tell sql server really make changes.