I would like your help to make a stored procedure to update the stock table . At first I am storing all the bill items in a temporary table and then once save it would deduct the stock from the stock table and it would then store it in Bill_Item table and Temp_Bill_Item table will be deleted.
Neither the temp table nor cursors are necessary for this:
update stock_table
set qty = qty - bi.qty
from stock_table st
join bill_items bi
on bi.item_id = st.item_id
and bi.itemcode = st.itemcode;
This assumes that each item is only present once in the bill_items table. If you can have multiple rows with the same itemid/itemcode you need a slightly different statement:
update stock_table
set qty = qty - bi.total_qty
from stock_table st
join (select item_id, itemcode, sum(qty) as total_qty
from bill_items
group by item_id, itemcode
) bi
on bi.item_id = st.item_id
and bi.itemcode = st.itemcode;
(Syntax not tested as you did not post sample data to play with)
For your purposes I would look into using a cursor.
http://msdn.microsoft.com/en-us/library/ms180169.aspx
Beware that using cursors in SQL has serious performance implications if you're not careful!
