Comments by ahmed yousif (Top 3 by date)

after execute the query it returns '0 rows affected' means nothing changed in stock columns (there is no any error messages)
what iam expecting,
* if i sold 1 full box + 0 strips==> only box column decrease by 1 nothing happen to strips column
------------------------------------------------
* if i sold only 10 tablets(one strip)==> here there is two probabilities:

1- if column strips already contains stock from this item for example, 10 tablets
then column strips should decrease by 10 tablets (column box remains as it is)

2- if column strips is 0 stock for this item,for example i have 3 boxes : 0 loose
and i sold only 10 tablets(one strip) now i want stock to be [2 boxes: 20 loose]
----------------------------------note-----------------------------------------
i predefined total number of tablets of each box (i.e: box of 30 tablets,50 tablets,....)
i hope i explained my issue

Dear originalgriff,
thank you for your reply
i tried another code as follow
now i have three tables
all_medicines ==> contains all medicine data
medicine_stock ==> medicine inventory
sales tables ==> parameters values
==========================================
create proc dbo.test_update
@a char(5),
@b smallint,
@c smallint
as
UPDATE dbo.medicine_stock
SET box = medicine_stock.box - @b,
loose = CASE
WHEN loose > 0 THEN loose - @c
WHEN loose = 0 THEN (box * all_medicines.box_content) - @c / all_medicines.box_content
WHEN loose = 0 THEN all_medicines.box_content - @c
END
FROM dbo.all_medicines
INNER JOIN dbo.medicine_stock
ON dbo.all_medicines.medicine_id = dbo.medicine_stock.medicine_id
where dbo.medicine_id = @a;
-----------------------note-------------------------------
box_content column from all_medicines table represent number of tablets per box
this code does not work
what should i do ??
thanks in advance