This stored procedure accepts a SubCategoryID and returns all the products in the Product table in that category. This list is used to populate the _menu user control, and is the engine behind the product list page.
Definition:CREATE Procedure usp_ProductsBySubcategory ( @SubcategoryID int, @Culture nvarchar(10) ) AS SELECT P.ProductID, P.[Name], dbo.ConvertCurrency(P.ListPrice, @Culture).ToString() as ListPrice, PP.ThumbnailPhotoFileName, PSC.ProductCategoryID FROM Production.Product AS P LEFT OUTER JOIN Production.ProductSubcategory AS PSC on P.ProductSubcategoryID = PSC.ProductSubcategoryID LEFT OUTER JOIN Production.ProductProductPhoto AS PPP on P.ProductID=PPP.ProductID LEFT OUTER JOIN Production.ProductPhoto AS PP ON PP.ProductPhotoID=PPP.ProductPhotoID WHERE P.ProductSubcategoryID = @SubcategoryID AND P.ListPrice IS NOT NULL AND P.FinishedGoodsFlag = 1 AND (PPP.ProductID IS NULL OR PPP.[Primary] = 1 ) ORDER BY P.[Name], P.ProductID, PPP.ModifiedDate;Database Tables Used:
Product, ProductSubCategory, ProductPhoto:
The Product table contains the core information about all of the items for sale
on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.
The ProductSubCategory table contains a list of all subgroups of products stored in the database.
The ProductPhoto table contains information about the pictures of the products.