"usp_ProductsBySubCategory" Stored Procedure

Description:

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.