This stored procedure first creates a record in the Orders table for the order being created. Then, it adds all of the products from the user's shopping cart into the OrderDetails table, and then emptys the user's shopping cart. It performs all three of these steps in a transaction. This ensures that if an error occurs adding an item to the OrderDetails table, there will not be an empty order left in the Orders table.
Definition:CREATE Procedure OrdersAdd ( @CustomerID int, @CartID nvarchar(50), @OrderDate datetime, @ShipDate datetime, @OrderID int OUTPUT ) AS BEGIN TRAN AddOrder /* Create the Order header */ INSERT INTO Orders ( CustomerID, OrderDate, ShipDate ) VALUES ( @CustomerID, @OrderDate, @ShipDate ) 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 /* Removal of items from user's shopping cart will happen on the business layer*/ EXEC ShoppingCartEmpty @CartId COMMIT TRAN AddOrderDatabase Tables Used:
Orders: The Orders table contains the header information about all the orders in the system (i.e., all items that are actually purchased. Users must check out their cart to place an order in the system). When an order is created, an entry is made in the Orders table with the CustomerID, OrderDate and ShipDate. Then, any information about the products in the order is added to the OrderDetails table as discussed below. Note that if no OrderDate or ShipDate is provided, the current date is entered as default.
OrderDetails: The OrderDetails table contains detailed product information for each order in the system. For each product in an order, an entry is made into the OrderDetails table containing the ProductID, Quantity and current UnitCost of the item. There is a many to one relationship between OrderDetails table and the Orders table. Note the primary key in the table is both OrderID and ProductID (since those two columns are the minimum required to guarantee uniqueness of a record).
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.