Check a parameter value in SRPOC

[angelica]

Well,

I am going through one of my SRPOCs and I have some doubt about one of its values. It goes like this :

Update tb_book

SET stockbalance = stockbalance-@qty
LimitAlert= Case

When stockBalance-@qty<re_orderLimit

THEN 1
ELSE 0
END

Now my question is:

Is @qty being deleted twice , in the Set... and in the When.... ?

[wild_bill]

I would think you would want this, so stockBalance is not decrement by qty twice

Update tb_book

SET stockbalance = stockbalance-@qty
LimitAlert= Case

When stockBalance < re_orderLimit

THEN 1
ELSE 0
END

[szlamany]

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.

[szlamany]

Here is the proof - your use is shown in this post

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

returns

Value       Alert       
----------- ----------- 
5           0

Value       Alert       
----------- ----------- 
0           0

[szlamany]

And the proper way to do it

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

which returns

Value       Alert       
----------- ----------- 
5           0

Value       Alert       
----------- ----------- 
0           1

[angelica]

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.

[szlamany]

Yes

and Yes was to short a reply for the forum so I am forced to add this

[angelica]

Thanks