Updating Fields In Specified Order

perlmunger

I am trying to determine if there is a way to ensure that one field gets updated before another in my stored procedure. I am finding that I usually have to run the sproc twice to get the values that I want, but it seems to me that there must be an easier way. In the sproc below, I want to ensure that Profit gets updated, but I don't know how. Any ideas?

CREATE PROCEDURE [dbo].[UpdatePricePoint]

        (@StoreID \[int\],
         @StoreDate \[datetime\] )

AS
BEGIN

UPDATE TicketItems
SET
Cost =
(CASE WHEN TicketItems.Qty = 0
THEN
1
ELSE
TicketItems.Qty
END )
*
(CASE WHEN StoreItems.CurrentCasePack = 0
THEN
StoreItems.CurrentCaseCost
ELSE
StoreItems.CurrentCaseCost / StoreItems.CurrentCasePack
END),
NormalPrice =
CASE WHEN StoreItems.NormalMSU = 0
THEN
StoreItems.NormalUnitSell
ELSE
StoreItems.NormalUnitSell / StoreItems.NormalMSU
END,
NormalAmount =
(CASE WHEN StoreItems.NormalMSU = 0
THEN
StoreItems.NormalUnitSell
ELSE
StoreItems.NormalUnitSell / StoreItems.NormalMSU
END)
*
(CASE WHEN TicketItems.Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
PricePoint =
(TicketItems.Amount - TicketItems.DiscountAmount)
/
(CASE WHEN Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
Price =
TicketItems.Amount
/
(CASE WHEN Qty = 0
THEN
1
ELSE
TicketItems.Qty
END),
Uni

Colin Angus Mackay

Do multiple updates in a single stored procedure. e.g Don't update the profit in the first update, but create a second update to ensure the profit value is correct. Alternatively - duplicate the calculations in order to generate the profit rather than take values from columns that don't yet exist - or are old. Does this help?

Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums