"usp_ShoppingCartAddItem" Stored Procedure

Description:

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
    );
    END



Database 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.