October 1, 2002 - Advantages of Stored Procedures
October 1, 2002|
Advantages of Stored Procedures
Tips: October 2002
Yehuda Shiran, Ph.D.
CREATE Procedure CustomerAlsoBought ( @ProductID int ) As /* We want to take the top 5 products contained in the orders where someone has purchased the given Product */ SELECT TOP 5 OrderDetails.ProductID, Products.ModelName, SUM(OrderDetails.Quantity) as TotalNum FROM OrderDetails INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID WHERE OrderID IN ( /* This inner query should retrieve all orders that have contained the productID */ SELECT DISTINCT OrderID FROM OrderDetails WHERE ProductID = @ProductID ) AND OrderDetails.ProductID != @ProductID GROUP BY OrderDetails.ProductID, Products.ModelName ORDER BY TotalNum DESC GO