SQL Search 1

[gouber82]

I have this web page that does a search in a database. I want it to search for keywords in three columns. As far as I can tell it does this. It looks like the query is not getting the whole database though. The code uses a stored procedure that is pasted at the bottom. I have gone into the query analyzer and done a sql select like this ...

SELECT * FROM Products WHERE CONTAINS (columnName, 'searchItem')

This returns stuff from say the first half of the table.
When I use ...

SELECT * FROM Products WHERE LIKE (columnName, 'searchItem')

It returns the correct stuff. The stored procedure is using freetexttable, and I think that that is using the contains option. Is there any way to change what that uses, or let me know why I am not returning all info from the DB.

****************
Stored Procedure
****************

CREATE procedure uspGetSearchProducts2 (
@keywords varchar(100)
)
as
set nocount on

select p.ID pID,
(select top 1 cp.cID
from CategoryProducts cp
where cp.pID = p.ID) cID,
p.name, p.description, p.cost, t.filename, p.sImage, p.vendorSKU, p.sku
from Vendors v inner join Products p
on v.ID = p.vID inner join Templates t
on p.tID = t.ID inner join freetexttable(products, *, @keywords) ftt
on p.ID = ftt.[KEY]
where (freetext(p.name, @keywords) or
freetext(p.description, @keywords) or
freetext(p.vendorSKU, @keywords) or
freetext(p.sku, @keywords) or
freetext(p.keywords, @keywords)) and
(p.ID in(select pID
from categoryproducts)) and
(p.csfID = 1) and
(v.csfID = 1)
order by ftt.rank desc
GO

 

[SQLSister]

HAs your ful-text index been repopulated recently? That would account for why it can;t find all the records.

 

[gouber82]

I had wondered if there was some sort of update that was needed. How would I do something like that?

Thanks,
TJ

 

[SQLSister]

It might be best if you read about full-text indexing in Books Online. It can be a somewhat complex subject and you really need to have the comlete background if you are going to be using it.