This stored procedure first creates a record in the SalesOrderHeader table for the order being created. Then, it adds all of the products from the user's shopping cart into the SalesOrderDetail table, and then empties 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 SalesOrderDetail table, there will not be an empty order left in the SalesOrderHeader table.
Definition:CREATE Procedure usp_OrdersAdd ( @CustomerID int, @CartID nvarchar(50), @OrderDate datetime, @ShipDate datetime, @ShippingMethod int, @Status int, @BillToAddressID int, @ShipToAddressID int, @OrderID int OUTPUT ) WITH EXECUTE AS OWNER AS BEGIN TRANSACTION AddOrder; DECLARE @err int DECLARE @rc int DECLARE @ContactID int DECLARE @OrderTotal money SELECT @OrderTotal = Cast(sum(SCI.Quantity*P.ListPrice) as money) FROM Sales.ShoppingCartItem as SCI LEFT OUTER JOIN Production.Product as P ON SCI.ProductID = P.ProductID WHERE SCI.ShoppingCartID = @CartID; SELECT @err = @@error; IF (@err <> 0) GOTO abort SELECT @ContactID = ContactID FROM Sales.Individual WHERE CustomerID = @CustomerID; SELECT @err = @@error, @rc = @@rowcount; if (@err <> 0 OR @rc < 1) GOTO abort -- TODO: Come up with a better tax and freight story in a future release. -- Create the Order header INSERT INTO Sales.SalesOrderHeader ( CustomerID, SubTotal, TaxAmt, Freight, OrderDate, DueDate, ShipDate, ShipMethodID, ContactID, OnlineOrderFlag, Status, BillToAddressID, ShipToAddressID ) VALUES ( @CustomerID, @OrderTotal, 0, cast (@OrderTotal * .1 AS money), @OrderDate, @ShipDate, @ShipDate, @ShippingMethod, @ContactID, 1, @Status, @BillToAddressID, @ShipToAddressID ); SELECT @OrderID = SCOPE_IDENTITY(), @err = @@error; IF (@err <> 0) GOTO abort -- Copy items from given shopping cart to SalesOrderDetail table for given OrderID INSERT INTO Sales.SalesOrderDetail ( SalesOrderID, ProductID, SpecialOfferID, OrderQty, UnitPrice ) (SELECT @OrderID, SCI.ProductID, 1, -- No discount SCI.Quantity, P.ListPrice FROM Sales.ShoppingCartItem as SCI INNER JOIN Production.Product as P ON SCI.ProductID = P.ProductID WHERE SCI.ShoppingCartID = @CartID) SELECT @err = @@error; IF (@err <> 0) GOTO abort EXEC usp_ShoppingCartEmpty @CartID; COMMIT TRANSACTION AddOrder; RETURN (0); abort: ROLLBACK TRANSACTION AddOrder RETURN (@err);Database Tables Used:
SalesOrderHeader: The SalesOrderHeader table contains the header information about all the orders in the system (i.e., all sales transactions that are actually completed. Users must check out their cart to place an order in the system). When an order is created, an entry is made in the SalesOrderHeader table with the CustomerID, OrderDate and ShipDate. Then, any information about the products in the order is added to the SaleOrderDetail table as discussed below. Note that if no OrderDate or ShipDate is provided, the current date is entered as default.
SalesOrderDetail: The SalesOrderDetail table contains detailed product information for each order in the system. For each product in an order, an entry is made into the SalesOrderDetail table containing the ProductID, Quantity and current UnitCost of the item. There is a many to one relationship between SalesOrderDetail table and the SalesOrderHeader table. Note the primary key in the table is both SalesOrderID and LineNumber (since those two columns are the minimum required to guarantee uniqueness of a record).
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.