This stored procedure accepts a 255 character string and performs the TSQL 'LIKE' command on ModelNumber, ModelName and Description fields of the Product table. It returns a recordset of matched items.
Definition:CREATE Procedure usp_ProductSearch ( @Search nvarchar(255), @Language nchar(2), @Culture nvarchar(10) ) AS SELECT P.ProductID, P.[Name], P.ProductNumber, dbo.ConvertCurrency(P.ListPrice, @Culture).ToString() as ListPrice, PP.ThumbnailPhotoFileName 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.[Name] LIKE N'%' + @Search + N'%' OR P.ProductNumber LIKE N'%' + @Search + N'%' OR PD.[Description] LIKE N'%' + @Search + N'%' ) AND P.ListPrice IS NOT NULL AND P.FinishedGoodsFlag = 1 AND PPP.[Primary] = 1Database 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.