"usp_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 usp_OrdersList
(
    @CustomerID int,
	@Culture nvarchar(10)

)
AS

SELECT  
    SOH.SalesOrderID,
    dbo.ConvertCurrency(sum(SOD.OrderQty*SOD.UnitPrice), @Culture).ToString() as OrderTotal,
    SOH.OrderDate, 
    SOH.ShipDate,
	SCM.StatusMessage

FROM    
    Sales.SalesOrderHeader AS SOH
  INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
  INNER JOIN dbo.StatusCodeMessage AS SCM ON SOH.Status = SCM.StatusCode

GROUP BY    
    SOH.CustomerID, 
    SOH.SalesOrderID, 
    SOH.OrderDate, 
    SOH.ShipDate,
	SCM.StatusMessage
HAVING  
    SOH.CustomerID = @CustomerID;


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