Check for duplicate CartID during Order insertion into SQL Server db using NOT EXISTS
To prevent a duplicate shopping cart being added I am attempting to modify my stored procedure to use NOT EXISTS. I have used this before in the WHERE statement but this one is a puzzle. How do I apply a NOT EXISTS to Orders insert that quits the entire process if the CartID already exists.
开发者_高级运维 ALTER PROCEDURE [dbo].[CreateCustomerOrder]
(@CartID char(36),
@CustomerID uniqueidentifier,
@ShippingID int,
@TaxID int)
AS
/* Insert a new record into Orders */
DECLARE @OrderID int
INSERT INTO Orders (CustomerID, ShippingID, TaxID)
VALUES (@CustomerID, @ShippingID, @TaxID)
/* Save the new Order ID */
SET @OrderID = @@IDENTITY
/* Add the order details to OrderDetail */
INSERT INTO OrderDetail
(OrderID, ProductID, ProductName, Quantity, UnitCost)
SELECT
@OrderID, Product.ProductID, Product.Name,
ShoppingCart.Quantity, Product.Price
FROM Product JOIN ShoppingCart
ON Product.ProductID = ShoppingCart.ProductID
WHERE ShoppingCart.CartID = @CartID
/* Update Product Inventory */
UPDATE p
SET inventory = p.inventory - b.qty
FROM product as p,
(SELECT productid
,sum(quantity) as qty
FROM shoppingcart
WHERE cartid=@CartID
GROUP BY productid
) b
WHERE p.productid=b.productid
/* Clear the shopping cart */
DELETE FROM ShoppingCart
WHERE CartID = @CartID
/* Return the Order ID */
SELECT @OrderID
Change the insert to :
INSERT INTO OrderDetail
(OrderID, ProductID, ProductName, Quantity, UnitCost)
SELECT @OrderID, Product.ProductID, Product.Name,
ShoppingCart.Quantity, Product.Price
FROM Product JOIN ShoppingCart
ON Product.ProductID = ShoppingCart.ProductID
WHERE NOT EXISTS (SELECT 1 FROM ShoppingCart a WHERE a.CartID = @CartID)
精彩评论