October 1, 2002 - Advantages of Stored Procedures | WebReference

October 1, 2002 - Advantages of Stored Procedures

Yehuda Shiran October 1, 2002
Advantages of Stored Procedures
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

IBuySpy Store uses stored procedure extensively. These are SQL scripts that are stored permanently in the MSSQL database, and are called from within the JScript .NET code. In fact, all interactions with the database are accomplished via stored procedures. There are several advantages to using stored procedures instead of writing SQL queries in the JScript .NET code. Performance and ease of maintenance are two obvious reasons to use stored procedures. To view IBuySpy's stored procedures, you need to open MSSQL Enterprise, and look under the StoreDOCJS database for the Stored Procedures entry. You will find 23 stored procedures. Double click one of the stored procedures to view its content. Here is the CustomerAlsoBought stored procedure:

  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