"usp_ProductDetail" Stored Procedure

Description:

This stored procedure accepts a ProductID as an input parameter and returns product information to the caller in the form of output parameters. A one line recordset is not used in order to gain a performance increase. The data returned by this stored procedures is used primarily on the product details screen.

Definition:
CREATE Procedure usp_ProductDetail
(
    @ProductID    int,
	@Language	  nchar(2),
	@Culture	  nvarchar (10),
    @ModelNumber  nvarchar (25) OUTPUT,
    @ModelName    nvarchar (50) OUTPUT,
    @ProductImage nvarchar (50) OUTPUT,
    @UnitCost     nvarchar (20) OUTPUT,
    @Description  nvarchar (4000) OUTPUT
)
AS


SELECT TOP 1
    @ModelNumber  = P.ProductNumber,
    @ModelName    = P.[Name],
    @ProductImage = PP.LargePhotoFileName,
    @UnitCost     = dbo.ConvertCurrency(P.ListPrice, @Culture).ToString(),
    @Description  = PD.Description

FROM 
    Production.Product AS P
 
 LEFT OUTER JOIN Production.ProductProductPhoto AS PPP on P.ProductID=PPP.ProductID
 LEFT OUTER JOIN Production.ProductPhoto AS PP ON PP.ProductPhotoID=PPP.ProductPhotoID
 LEFT OUTER JOIN Production.ProductModel AS PM ON P.ProductModelID=PM.ProductModelID
 LEFT OUTER JOIN Production.ProductModelProductDescriptionCulture AS PMPDL 
   ON (PM.ProductModelID=PMPDL.ProductModelID AND PMPDL.CultureID=@Language)
 LEFT OUTER JOIN Production.ProductDescription AS PD
   ON PMPDL.ProductDescriptionID=PD.ProductDescriptionID

WHERE 
    P.ProductID = @ProductID AND PPP.[Primary] = 1

ORDER BY PP.ModifiedDate ASC;


Database Tables Used:

Product, ProductPhoto, ProductModel, ProductDescription, ProductModelXProductDescriptionXLocale:  
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 ProductPhoto table contains information about the pictures of the products.
The ProductModel table contains information about a given type of product (irrespective of such issues as size).
The ProductDescription table contains textual information about one or more product models.
The ProductModelXProductDescriptionXLocale table associates particular types of products with information about those kinds of products and the languages for which that information is appropriate.