Building e-Commerce Sites with the .NET Framework

Developing the Stored Procedures

After we built a structure to house the product data, we needed to create the stored procedures for selecting, adding, updating, and deleting product categories, parts, and part options.

Retrieving Category Information

The stored procedure spGetCategories was created to retrieve all top-level categories. Listing 4.1 shows this stored procedure.

Listing 4.1. spGetCategories Code

CREATE PROCEDURE spGetCategories AS SELECT CategoryID, Name FROM Category WHERE Display <> 0 AND ParentCategoryId Is Null ORDER BY DisplaySeq

This query is pretty straightforward, but I want to mention a couple of interesting points. The AND line checks that the ParentCategoryID field is null because only child categories have a ParentCategoryID ...