I have cut this down to keep it simple but here goes...
I have a table with 4 columns - OrderID, InitialOrderAmount, RemainingOrderAmount, CompletedDate
Rule is when the remaining order amount is 0, set the completed date.
I have a stored proc that takes in the ID, and the an order amount filled so I want to substract the amount filled from the remaining amount and if its 0 set the completed date.
my code is something like this...
ok so what I believed would happen is that even though the update statement updates the RemainingOrderAmount first, the case statement has to test by subtracting the amount as the first part of the update hasnt been committed... so I need the current value and subtract the amount filled.
Is this correct.
The reason I ask, is that in a test run... 13000 records updated fine but 4 had the maths right in that the RemainingOrderAmount was set to 0 but the completed date didnt get set.Any ideas... thanks Mick
OK... just want to throw this out there... is there any chance that a call to GETUTCDATE() could return NULL. I know its a bit mad to think but it would explain the issue alright.
What I was going to do was clean down the database and try this again to see will it happen. It can take hours. If it does... then replace the GETUTCDATE with a harded coded value and then try again.
Opinions very welcome...regards mIck
answered Oct 26 '10 at 09:13 AM