moved stock minus in item table to stock in itemmoment table

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...