asked on

Web search on SQL database fails on backslash

We have a web site and if you search for a product code with 2 slashes e.g. 123/456/7 in it fails. The search seems to be controlled from a stored procedure (see below) can anyone help?

-- Search Products (name,Code)
insert into @SearchResults (ResultID,ResultName,ResultType,ResultLink,ResultDescription,ResultImage,ResultMixed,Sort,ResultDescription2)
select DISTINCT OurCompany_Product.ProductID, OurCompany_Product.[Name],'Products','Product.aspx?ProductID='+cast(OurCompany_Product.ProductID as varchar),
cast(OurCompany_Product.Description as varchar(5000)),OurCompany_Product.ImageUrl,Code,1,cast(OurCompany_Product.Summary as varchar(5000))
from OurCompany_Product
left outer join OurCompany_Product_ProductCategory on OurCompany_Product.ProductID=OurCompany_Product_ProductCategory.ProductID
INNER JOIN   OurCompany_ProductCategory ON OurCompany_Product_ProductCategory.ProductCategoryID = OurCompany_ProductCategory.ProductCategoryID
where (OurCompany_Product.[Name] like '%'+@SearchText +'%' or Code like '%'+@SearchText +'%') AND OurCompany_Product_ProductCategory.ProductCategoryID <> @GenAccessID AND OurCompany_Product.Live = 1
AND Code NOT IN (SELECT ResultMixed FROM @SearchResults) AND OurCompany_Product_ProductCategory.ProductCategoryID <> @SparePartID;

Open in new window

SOLUTION