"usp_OrdersAdd" Stored Procedure

Description:

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.