"OrdersList" Stored Procedure

Description:

This stored procedure accepts a CustomerID as input and returns a list of all the user's orders in the Orders table. This procedure is used on the OrdersList.aspx page.

Definition:

    CREATE Procedure OrdersList
    (
        @CustomerID int
    )
    As

    SELECT
        Orders.OrderID,
        Cast(sum(orderdetails.quantity*orderdetails.unitcost) as money) as OrderTotal,
        Orders.OrderDate,
        Orders.ShipDate

    FROM
        Orders
      INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

    GROUP BY
        CustomerID,
        Orders.OrderID,
        Orders.OrderDate,
        Orders.ShipDate
    HAVING
        Orders.CustomerID = @CustomerID
        
Database 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).