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


Ten Most Expensive Products Top
create procedure "Ten Most Expensive Products" AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC



Dependencies Top
Name Type Updated Selected Column
dbo.Products user table no no ProductName
dbo.Products user table no no UnitPrice
Tables Views Stored Procedures Indexes



Employee Sales by Country Top
create procedure "Employee Sales by Country"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date



Dependencies Top
Name Type Updated Selected Column
dbo.Employees user table no no Country
dbo.Employees user table no no EmployeeID
dbo.Employees user table no no LastName
dbo.Employees user table no no FirstName
dbo.Orders user table no no OrderID
dbo.Orders user table no no EmployeeID
dbo.Orders user table no no ShippedDate
dbo.Order Subtotals view no no OrderID
dbo.Order Subtotals view no no Subtotal
Tables Views Stored Procedures Indexes



Sales by Year Top
create procedure "Sales by Year"
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date



Dependencies Top
Name Type Updated Selected Column
dbo.Orders user table no no OrderID
dbo.Orders user table no no ShippedDate
dbo.Order Subtotals view no no OrderID
dbo.Order Subtotals view no no Subtotal
Tables Views Stored Procedures Indexes



CustOrdersDetail Top

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
UnitPrice=ROUND(Od.UnitPrice, 2),
Quantity,
Discount=CONVERT(int, Discount * 100),
ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID



Dependencies Top
Name Type Updated Selected Column
dbo.Order Details user table no no Discount
dbo.Order Details user table no no ProductID
dbo.Order Details user table no no UnitPrice
dbo.Order Details user table no no Quantity
dbo.Products user table no no ProductID
dbo.Products user table no no ProductName
dbo.Order Details user table no no OrderID
Tables Views Stored Procedures Indexes



CustOrdersOrders Top

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID



Dependencies Top
Name Type Updated Selected Column
dbo.Orders user table no no RequiredDate
dbo.Orders user table no no ShippedDate
dbo.Orders user table no no OrderID
dbo.Orders user table no no CustomerID
dbo.Orders user table no no OrderDate
Tables Views Stored Procedures Indexes