orderspercustomer.txt

来自「Mastering VBNet Include Source Code」· 文本 代码 · 共 21 行

The StoredProcedure project requires that the OrdersPerCustomer stored procedure be attached to the database. The stored procedure's listing is shown here:

CREATE PROCEDURE dbo.OrdersPerCustomer
       @CustomerID nchar(5)='ALFKI'
AS
SELECT   dbo.Customers.CompanyName,
         COUNT(dbo.Orders.OrderID) AS [Total Orders],
         dbo.Customers.CustomerID,
         CAST(SUM((dbo.[Order Details].UnitPrice *
            dbo.[Order Details].Quantity) *
            (1 - dbo.[Order Details].Discount)) AS money) AS [Total Amount]
FROM     dbo.Customers
         INNER JOIN dbo.Orders
            ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
         INNER JOIN dbo.[Order Details]
            ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
GROUP BY dbo.Customers.CompanyName, dbo.Customers.CustomerID
HAVING   (dbo.Customers.CustomerID = @CustomerID)

GO