helo all...,
i want to make procedure like:
example
i have table:
item (itemid,itemname,stock)
orderdetail(no_order,itemid,quantity)
itemmoment(itemid,itemname,stock)
item table
itemid itemname stock
c1 coconut 2
p1 peanut 2
orderdetail table
no_order itemid quantity
1 c1 5
itemmoment table
itemid itemname stock
c1 coconut 0
p1 peanut 0
when customer paid, his quantity in orderdetail decrease stock in item table..
so stock in item table became:
itemid itemname stock
c1 coconut -3
p1 peanut 2
it's not good, because stock may not minus...
so i want to move -3 to itemmoment table..
so stock in item table became:
itemid itemname stock
c1 coconut 0
p1 peanut 2
and in itemmoment table became:
itemid itemname stock
c1 coconut 3
p1 peanut 0
my store procedure like:
ALTER PROCEDURE [dbo].[orders]
(
@.no_order as integer,
@.itemid AS varchar(50),
@.quantity AS INT
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @.currentStock AS INT
SET @.currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @.itemid)
UPDATE [item]
SET
[Stock] = @.currentStock - @.quantity
WHERE
[itemid] = @.itemid
COMMIT TRANSACTION
END
it's only decrease stock with quantity. i want move stock minus from item to itemmoment..
can anyone add code to my store procedure?
plss.. helpp.
thxx...
Here's how you would tackle the issue.
Declare @.AvailableQtySelect @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemIdIF @.AvailableQty >= @.QuantityBegin-- We have enough quantityUpdate ItemSet Stock = Stock - @.QuantityWhere ItemId = @.ItemIdEndElseBegin-- We have less quantityUpdate ItemSet Stock = 0Where ItemId = @.ItemIdInsert into ItemMoment (itemid,itemname, stock )Select @.ItemId, ItemName, (@.Quantity - @.AvailableQty )From ItemWhere Itemid = @.ItemIdEnd|||
thx ndinakar...
it's one problem in ur store procedure, but i have fix it.
Select @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemId
change to
set @.AvailableQty = (select stock FROM item Where itemid = @.itemid)
it have working...
ok, thx...
