quote:

---

Originally posted by spirit1

this doesn't work?
exec yourProc @OrderId = 12345 -- or your order number

maybe you should show us your sproc

I didn't try it on the command line (can you tell me how do I do that...I'm a GUI guy). I was trying it using right click on stored procedure -> then execute stored procedure.

Here it is....

CREATE PROCEDURE sp_DeductOrderFromInventory
@OrderID int = 0,
@oProductID int = 0,
@oQty int = 0,
@NewQty int = 0

AS
BEGIN
SET NOCOUNT ON;

WHILE (SELECT * FROM Custodial_Order_Details WHERE OrderID = OrderID)
BEGIN

SELECT @oProductID = ProductID, @oQTY = QTY FROM
Custodial_Order_Details
WHERE OrderID = @OrderID

SELECT @NewQTY = SUM(QTY - @oQTY)
FROM custodial_inventory
WHERE ProductID = @oProductID

UPDATE custodial_inventory
SET QTY = @newQTY
WHERE ProductID = @oProductID
END

END
GO

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

---

CREATE PROCEDURE sp_DeductOrderFromInventory 
@OrderID int
AS
BEGIN
SET NOCOUNT ON;

UPDATE	ci
SET		QTY = SUM(ci.QTY-cod.QTY)
FROM	custodial_inventory ci
		JOIN Custodial_Order_Details cod ON ci.ProductID = cod.ProductID
WHERE	OrderID = @OrderID
END
GO