"OrdersDetail" Stored Procedure

Description:

This stored procedure accepts an OrderID as input and returns a recordset containing line items associated with the order and 4 output parameters containing details of the given OrderID.

Definition:
CREATE Procedure usp_OrdersDetail
(
    @OrderID    int,
	@Culture nvarchar(10),
    @OrderDate  datetime OUTPUT,
    @ShipDate   datetime OUTPUT,
    @OrderTotal nvarchar(20) OUTPUT
)
AS

-- Return the order dates from the Orders
--   Also verifies the order exists for this customer. 


SELECT 
    @OrderDate = OrderDate,
    @ShipDate = ShipDate
    
FROM    
    Sales.SalesOrderHeader
    
WHERE   
    SalesOrderID = @OrderID;


IF @@Rowcount = 1
BEGIN


/* First, return the OrderTotal out param */
SELECT  
    @OrderTotal = dbo.ConvertCurrency(SUM(SOD.OrderQty * SOD.UnitPrice), @Culture).ToString()
    
FROM    
    Sales.SalesOrderDetail AS SOD
    
WHERE   
    SalesOrderID= @OrderID;

/* Then, return the recordset of info */
SELECT  
    P.ProductID, 
    P.[Name],
    P.ProductNumber,
    dbo.ConvertCurrency(SOD.UnitPrice, @Culture).ToString() as UnitPrice,
    SOD.OrderQty,
    dbo.ConvertCurrency(SOD.OrderQty * SOD.UnitPrice, @Culture).ToString() as ExtendedAmount

FROM
    Sales.SalesOrderDetail AS SOD
  INNER JOIN Production.Product as P ON SOD.ProductID = P.ProductID
  
WHERE   
    SalesOrderID = @OrderID
ORDER BY
      SOD.SalesOrderDetailID;

END


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

Product:  The Product table contains the core information about all of the items for sale on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.