help me search for products table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help me search for products table

Author

Topic

bhupesh238
Starting Member

1 Post

Posted - 2009-08-16 : 20:48:27

Hi,
I have a products table with their name and description as column. I want to search for the products from that table which joins other category, department and supplier table also. below is the search stored procedure I am using. But it is not searching for the number. for example if i search for 'intel e8400' it does search for product. even if i type 'intel e84', it searches for the product but for 'intel 8400'

Please help me get the desired results.

ALTER PROCEDURE dbo.freetextSearch

@cslist nvarchar(1024),
@keyword nvarchar(1024) ,
@instock int = NULL,
@supplier int = NULL,
@department int = -1,
@subcategory int = NULL,
@branch int = -1
AS
declare @x nvarchar(1024)
declare @y nvarchar(1024)
set @x = @cslist
set @y = '"*' + @keyword + '*"'

SELECT searchtbl.RANK, searchtbl.Expr1, searchtbl.TOTAL, searchtbl.ProductName, searchtbl.Description,
searchtbl.ProductId, searchtbl.SubCategoryId, searchtbl.SupplierId, searchtbl.BrandId,
searchtbl.SupplierCode, searchtbl.SKU, searchtbl.Barcode, searchtbl.Cost, searchtbl.Price, searchtbl.rrp,
searchtbl.ResellerPrice, searchtbl.WebPrice, searchtbl.Active, searchtbl.CreatedDate, searchtbl.imageType,
searchtbl.HideFromWeb, searchtbl.supplierStock, searchtbl.Special, searchtbl.Clearance, searchtbl.iLenght,
searchtbl.iHeight, searchtbl.iWidth, searchtbl.iWeight, searchtbl.markup, searchtbl.pmarkup, searchtbl.stock,
searchtbl.priceudn, searchtbl.priceudo, searchtbl.priceudb, searchtbl.priceudm, searchtbl.priceudd, searchtbl.Acc,
searchtbl.BrandName
FROM (SELECT KEY_TBL.RANK, ISNULL(descript.RANK, 0) AS Expr1,
ISNULL(ISNULL(KEY_TBL.RANK, 0) + ISNULL(descript.RANK, 0), 0) AS TOTAL,
FT_TBL.ProductId, FT_TBL.SubCategoryId, FT_TBL.SupplierId, FT_TBL.BrandId,
FT_TBL.SupplierCode, FT_TBL.SKU, FT_TBL.Barcode, FT_TBL.ProductName, FT_TBL.Description,
FT_TBL.Cost, FT_TBL.Price, FT_TBL.rrp, FT_TBL.ResellerPrice, FT_TBL.WebPrice,
FT_TBL.Active, FT_TBL.CreatedDate, FT_TBL.imageType, FT_TBL.HideFromWeb, FT_TBL.supplierStock, FT_TBL.Special,
FT_TBL.Clearance, FT_TBL.iLenght, FT_TBL.iHeight, FT_TBL.iWidth, FT_TBL.iWeight, FT_TBL.markup,
FT_TBL.pmarkup, FT_TBL.stock, FT_TBL.priceudn, FT_TBL.priceudo, FT_TBL.priceudb, FT_TBL.priceudm, FT_TBL.priceudd,
(SELECT Acc FROM t_Suppliers WHERE (SupplierId = FT_TBL.SupplierId)) AS Acc,
(SELECT BrandName FROM t_Brands WHERE (BrandId = FT_TBL.BrandId)) AS BrandName
FROM t_Products AS FT_TBL INNER JOIN
CONTAINSTABLE(t_Products, productname, @x) AS KEY_TBL ON
FT_TBL.ProductId = KEY_TBL.[KEY] LEFT OUTER JOIN
CONTAINSTABLE(t_Products, description, @y) AS descript ON
FT_TBL.ProductId = descript.[KEY]
WHERE (FT_TBL.Active = 1) AND (FT_TBL.HideFromWeb = 0) AND
(@supplier IS NULL OR ft_tbl.supplierid = @supplier) AND
(@instock IS NULL OR ft_tbl.stock > 0)
) AS searchtbl LEFT OUTER JOIN
--FOLLOWING QUERY IS TO SEARCH SKU AND SUPPLIER CODE. WE ARE LEFT JOINING IT TO THE FULL TEXT SEARCH SO THAT IF THE PRODUCT NAME CONTAINS THE
--SKU AND/OR SUPPLIER, JOIN WILL NOT LIST IS AGAIN. :)
(SELECT rank, Expr1, total, ProductId, SubCategoryId, SupplierId, BrandId, SupplierCode, SKU, Barcode,
ProductName, Description, Cost, Price, rrp, ResellerPrice, WebPrice, Active, CreatedDate,
imageType, HideFromWeb, supplierStock, Special, Clearance, iLenght, iHeight, iWidth, iWeight, markup,
pmarkup, stock, priceudn, priceudo, priceudb, priceudm, priceudd, Acc, BrandName
FROM (SELECT 0 AS rank, 0 AS Expr1, 0 AS total, ProductId, SubCategoryId, SupplierId, BrandId,
SupplierCode, SKU, Barcode, ProductName, Description, Cost, Price, rrp, ResellerPrice,
WebPrice, Active, CreatedDate, imageType, HideFromWeb, supplierStock, Special,
Clearance, iLenght, iHeight, iWidth, iWeight, markup, pmarkup, stock, priceudn, priceudo,
priceudb, priceudm, priceudd,
(SELECT Acc FROM t_Suppliers AS t_Suppliers_1 WHERE (SupplierId = t_Products.SupplierId)) AS Acc,
(SELECT BrandName FROM t_Brands AS t_Brands_1 WHERE (BrandId = t_Products.BrandId)) AS BrandName
FROM t_Products
WHERE (SKU LIKE '%'+@keyword+'%') AND (Active = 1) AND (HideFromWeb = 0) AND (@supplier IS NULL OR t_Products.supplierid = @supplier) AND
(@instock IS NULL OR t_Products.stock > 0)
OR
(SupplierCode LIKE '%'+@keyword+'%') AND (Active = 1) AND (HideFromWeb = 0) AND (@supplier IS NULL OR t_Products.supplierid = @supplier) AND
(@instock IS NULL OR t_Products.stock > 0)
) AS temptbl) AS temptbl2 ON
searchtbl.ProductId = temptbl2.ProductId
ORDER BY searchtbl.TOTAL DESC, searchtbl.RANK DESC, searchtbl.Expr1 DESC

irfanshirur
Starting Member

21 Posts

Posted - 2009-08-17 : 02:23:52

Hello

try using the FreeText Or Contains keywords to search for the Product Value .

WHERE FREETEXT(Description, @SearchWord);

Or

CONTAINS(Description, @SearchWord);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources