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; ENDDatabase 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.