x

Point of update when using case in an Update statement

hi there,

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...

UPDATE [OrderTable] SET RemainingOrderAmount = RemainingOrderAmount - ABS(@orderAmountFilled ),        -- reduce the amount left to close
                  ClosingTime = (CASE WHEN (RemainingOrderAmount - ABS(@orderAmountFilled)) = 0 THEN GETUTCDATE()
                              ELSE NULL END)                                                              
    WHERE OrderID = @orderID

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
more ▼

asked Oct 26, 2010 at 05:01 AM in Default

Doiremik gravatar image

Doiremik
71 6 6 7

looks like it should work, is there more to this that might be affecting the result?
Oct 26, 2010 at 05:32 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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
more ▼

answered Oct 26, 2010 at 09:13 AM

Doiremik gravatar image

Doiremik
71 6 6 7

GETUTCDATE() cannot return NULL as far as I am aware. It just passes the time coming from the O/S - if that doesn't work, you're in a whole other world of pain!
Oct 27, 2010 at 12:06 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x131
x40

asked: Oct 26, 2010 at 05:01 AM

Seen: 1434 times

Last Updated: Oct 26, 2010 at 12:20 PM