Printable View
Well,
I am going through one of my SRPOCs and I have some doubt about one of its values. It goes like this :
Now my question is:Code:Update tb_book
SET stockbalance = stockbalance-@qty
LimitAlert= Case
When stockBalance-@qty<re_orderLimit
THEN 1
ELSE 0
END
Is @qty being deleted twice , in the Set... and in the When.... ?
I would think you would want this, so stockBalance is not decrement by qty twice
Code:Update tb_book
SET stockbalance = stockbalance-@qty
LimitAlert= Case
When stockBalance < re_orderLimit
THEN 1
ELSE 0
END
First - you are missing a comma - right. That syntax won't fly...
But a field's value is "original" value everywhere you refer to it in a single UPDATE query. Just because it's one-line up in the query - that you are doing
stockbalance = stockbalance-@qty
The stockbalance is not reduced in the CASE statement - it's original value.
Here is the proof - your use is shown in this post
returnsCode:Set NoCount On
Declare @Test Table (Value int, Alert int)
Insert into @Test values (10,0)
Declare @Qty int
Set @Qty=5
Update @Test Set Value=Value-@Qty, Alert=Case When Value<=0 Then 1 Else 0 End
Select * From @Test
Update @Test Set Value=Value-@Qty, Alert=Case When Value<=0 Then 1 Else 0 End
Select * From @Test
Code:Value Alert
----------- -----------
5 0
Value Alert
----------- -----------
0 0
And the proper way to do it
which returnsCode:Set NoCount On
Declare @Test Table (Value int, Alert int)
Insert into @Test values (10,0)
Declare @Qty int
Set @Qty=5
Update @Test Set Value=Value-@Qty, Alert=Case When Value-@Qty<=0 Then 1 Else 0 End
Select * From @Test
Update @Test Set Value=Value-@Qty, Alert=Case When Value-@Qty<=0 Then 1 Else 0 End
Select * From @Test
Code:Value Alert
----------- -----------
5 0
Value Alert
----------- -----------
0 1
ok Sslamany,
So apart from the comma which is my typo, the SPROC is okay and it is deducting only once. More convinced now.
thanks.
Yes
and Yes was to short a reply for the forum so I am forced to add this ;)
Thanks:D