Need Help with Stored Procedure

[maxf]

Im trying to modify the IbuySpy Store and need help with a Stored procedure. Right now Im running this code which works:

SELECT
@OrderID = @@Identity

/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)

SELECT
@OrderID,
ShoppingCart.ProductID,
Quantity,
Products.UnitCost

FROM
ShoppingCart
INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID

WHERE
CartID = @CartID


After this code, I want to run an Update Statement on the Products Table and Decrease the QuantityInStock Column (int) by the Quantity from the ShoppingCart table for each product in the shopping cart. Ive got this code working if there is only one item in the shopping cart:

Begin

UPDATE
PRODUCTS Set QuantityInStock = Cast((SELECT QuantityInStock FROM Products INNER JOIN ShoppingCart ON Products.ProductID = ShoppingCart.ProductID WHERE CartID = @CartID) As Int) - Cast((Select Quantity From ShoppingCart INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID WHERE CartID = @CartID) As Int)
WHERE ProductID = (Select Products.ProductID From Products Inner Join ShoppingCart ON Products.ProductID = ShoppingCart.ProductID WHERE CartID = @CartID)

End

The problem is that if there is more than one product in the shopping cart when this code runs, I get an error that multiple return values were found. My question is, how do I run an update statement on multiple rows -- basically need to loop through all the products and decrease the QuantityInStock column by the Quantity in the Shoppingcart table (for each ProductID for a given CartID)

 

[tlbroadbent]

It is much simpler than you are making it. This assumes that you've alrready checked to make sure the Products table has a positive quantity.

UPDATE PRODUCTS Set
QuantityInStock = PRODUCTS.QuantityInStock - ShoppingCart.Quantity
FROM PRODUCTS
INNER JOIN ShoppingCart
ON Products.ProductID = ShoppingCart.ProductID
WHERE ShoppingCart.CartID = @CartID


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm