There are two tables one is Product_mst and other is Stock_Master I have one web page for placing order of multiple product im using XML to insert multiple product it is working fine but at the same time i want to update my Stock_master to reduce the stock qty for that i added update block on Order sp i want to update the stock product wise bt now it is updating to all rows Please help me please find my SP below
What I have tried:
ALTER PROCEDURE [dbo].[InsertOrderDtls]
(
@OrderXml XML
)
AS
BEGIN
INSERT INTO order_dtls (OrderId, ProdId, Rate,Qty, Amount)
SELECT p.value('@OrderId','INT'), p.value('@ProdId','INT'),
p.value('@Rate','FLOAT') ,p.value('@Qty','FLOAT'), p.value('@Amount','FLOAT')
--,p.value('@AddedBy','INT')
--,p.value('@AddedOn','SMALLDATETIME')
FROM @OrderXml.nodes('/ROOT/Order')n(p);
--RETURN @@ROWCOUNT;
update product_Mst
set Qty = Qty - q.value('@Qty','FLOAT')
from @OrderXml.nodes('/ROOT/Order')n(q);
--update Stock_Master
--set Qty = Qty - r.value('@Qty','FLOAT')
--from @OrderXml.nodes('/ROOT/Order')n(r)
--where ProductId = r.value('@ProdId','INT');
END