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 ShoppingCartAddItem ( @CartID nvarchar(50), @ProductID int, @Quantity int ) As DECLARE @CountItems int SELECT @CountItems = Count(ProductID) FROM ShoppingCart WHERE ProductID = @ProductID AND CartID = @CartID IF @CountItems > 0 /* There are items - update the current quantity */ UPDATE ShoppingCart SET Quantity = (@Quantity + ShoppingCart.Quantity) WHERE ProductID = @ProductID AND CartID = @CartID ELSE /* New entry for this Cart. Add a new record */ INSERT INTO ShoppingCart ( CartID, Quantity, ProductID ) VALUES ( @CartID, @Quantity, @ProductID )Database Tables Used:
ShoppingCart: The ShoppingCart table keeps track of the items a user has purchased. Its primary key is the RecordID field. The CartID is a string which we use to identify the user currently logged in. There is a many to one relationship between the ShoppingCart table and the Products table. Note that if not Quantity is supplied, a default of 1 is entered.