IBuySpy Store, Part II: JScript Components: Stored Procedures - Doc JavaScript

IBuySpy Store, Part II: JScript Components

Stored Procedures

IBuySpy Store uses stored procedures 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 IBuySpy 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.

Performance is better because you don't have to pass the procedure from the Web server to the MSSQL server every time you need to retrieve records from the database (usually the MSSQL server is different from the Web server).

Maintenance is easier because the MSSQL code is separate from the JScript .NET code. A JScript .NET expert can maintain the JScript .NET code without having to know too much about MSSQL and how to write SQL queries. An MSSQL expert can maintain the stored procedures without having to know too much about JScript .NET coding. You can use a new version of a stored procedure without touching the JScript .NET code that calls this procedure (assuming the input and output parameters did not change, of course). You can also write a new version of the JScript .NET component without touching the stored procedure. Aside from matching the input and output parameters, the JScript .NET code and the stored procedures are two different entities that can be developed and maintained independently. This separation contributes a lot to code maintainability.

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

This stored procedure expects one input parameter, ProductID. It selects records from the database according to this ProductID, and returns the records found by the query.