"ProductsMostPopular" Stored Procedure

Description:

This stored procedure returns the top 5 most purchased items in the IBuySpy application.  It is the engine behind the "Most popular items" user control.

Definition:

    CREATE Procedure ProductsMostPopular

    AS

    SELECT TOP 5
        OrderDetails.ProductID,
        SUM(OrderDetails.Quantity) as TotalNum,
        Products.ModelName

    FROM
        OrderDetails
      INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID

    GROUP BY
        OrderDetails.ProductID,
        Products.ModelName

    ORDER BY
        TotalNum DESC
        
Database Tables Used:

OrderDetails:  The OrderDetails table contains detailed product information for each order in the system.  For each product in an order, an entry is made into the OrderDetails table containing the ProductID, Quantity and current UnitCost of the item.  There is a many to one relationship between OrderDetails table and the Orders table.  Note the primary key in the table is both OrderID and ProductID (since those two columns are the minimum required to guarantee uniqueness of a record).

Products:  The Products table contains all of the information about all of the products on the IBuySpy web site. It's primary key is the ProductID identity field.  Note that product descriptions are limited to 3800 characters.