This stored procedure adds a product to the ShoppingCart for the given CartID. If the product does not exist in the current cart, it adds a new entry. If the product does already exist in the current CartID, it adds the quantity to the quantity currently in the database.
Definition:CREATE Procedure usp_ShoppingCartAddItem ( @CartID nvarchar(50), @ProductID int, @Quantity int ) AS DECLARE @CountItems int SELECT @CountItems = Count(ProductID) FROM Sales.ShoppingCartItem WHERE ProductID = @ProductID AND ShoppingCartID = @CartID; IF @CountItems > 0 -- There are items - update the current quantity UPDATE Sales.ShoppingCartItem SET Quantity = (@Quantity + Quantity) WHERE ProductID = @ProductID AND ShoppingCartID = @CartID; ELSE -- New entry for this Cart. Add a new record BEGIN INSERT INTO Sales.ShoppingCartItem ( ShoppingCartID, Quantity, ProductID ) VALUES ( @CartID, @Quantity, @ProductID ); ENDDatabase Tables Used:
ShoppingCartItem: The ShoppingCartItem table keeps track of the items a user has purchased. Its primary key is the ShoppingCartItemID field. The ShoppingCartID is a string which is used to identify the user who owns the basket of items. There is a many to one relationship between the ShoppingCartItem table and the Product table. Note that if not Quantity is supplied, a default of 1 is entered.