Database : Northwind
Report created by EasyStruct 4.5 on 8/16/2004
  Tables Views Stored Procedures  Indexes
 Stored Procedures


CustOrderHistTop
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName


DependenciesTop
NameTypeUpdatedSelectedColumn
dbo.Order Detailsuser tablenonoQuantity
dbo.Customersuser tablenonoCustomerID
dbo.Productsuser tablenonoProductName
dbo.Order Detailsuser tablenonoOrderID
dbo.Order Detailsuser tablenonoProductID
dbo.Ordersuser tablenonoOrderID
dbo.Ordersuser tablenonoCustomerID
dbo.Productsuser tablenonoProductID
TablesViewsStored ProceduresIndexes


SalesByCategoryTop
CREATE PROCEDURE SalesByCategory
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END

SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName


DependenciesTop
NameTypeUpdatedSelectedColumn
dbo.Order Detailsuser tablenonoDiscount
dbo.Categoriesuser tablenonoCategoryID
dbo.Categoriesuser tablenonoCategoryName
dbo.Order Detailsuser tablenonoProductID
dbo.Order Detailsuser tablenonoUnitPrice
dbo.Order Detailsuser tablenonoQuantity
dbo.Productsuser tablenonoProductName
dbo.Productsuser tablenonoCategoryID
dbo.Order Detailsuser tablenonoOrderID
dbo.Ordersuser tablenonoOrderID
dbo.Ordersuser tablenonoOrderDate
dbo.Productsuser tablenonoProductID
TablesViewsStored ProceduresIndexes