| Author |
 Topic  |
|
|
gouber
Starting Member
7 Posts |
 Posted - 18 December 2003 : 18:18:24
|
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 |
Edited by - ruirib on 18 December 2003 20:37:09 |
|
|
gouber
Starting Member
7 Posts |
Posted - 18 December 2003 : 18:59:43
|
Sorry I just noticed that the SQL query statements were typed wrong in the forum, I meant when I type ...
SELECT * FROM Products WHERE columnName LIKE '%searchItem%'
It works, buth when I type ...
SELECT * FROM Products WHERE CONTAINS (keywords, '%dog%')
it does not work. |
 |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 18 December 2003 : 19:11:28
|
what do you mean by does not work, does it give an error ?
do you have freetext catalogs setup for the keywords field ? |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 19 December 2003 : 04:27:02
|
You would need to use:
SELECT * FROM Products WHERE CONTAINS (keywords, 'dog')
or
SELECT * FROM Products WHERE CONTAINS (keywords, '*dog*')
and not include the % (unless you were actually searching for a word with a % in it) |
|
|
|
gouber
Starting Member
7 Posts |
Posted - 19 December 2003 : 11:31:28
|
HuwR:
When I mean that it does not work, I mean that the query statement does not return the results that contain the word that I am looking for. It does if it is an older entry in the database, but not the newer information. But I would not think that since I am pulling strait from the table that it would matter if the stored procedure makes another table or not. I guess that somehow it does.
pweighill:
I tried the query with an * before and after, it still did not return the results. I had already tried without and extra characters.
Thank you both for the help, I am still having trouble.
Thanks,
TJ |
|
|
|
gouber
Starting Member
7 Posts |
Posted - 19 December 2003 : 11:42:25
|
By what it looks like, I think that I might need to run the stored procedure somehow. If that is the case, can someone let me know how to do that?
Thanks,
TJ |
|
|
|
gouber
Starting Member
7 Posts |
Posted - 19 December 2003 : 17:51:01
|
I managed to do a recompile on the stored procedure and then I ran the procedure again. This still did not help. To recompile the procedure I did ...
EXEC sp_recompile 'procedureName'
I do not have a clue what to do past this.
Thanks,
TJ |
|
|
|
gouber
Starting Member
7 Posts |
Posted - 22 December 2003 : 12:31:01
|
| I figured it out, I was being so stupid. All I had to do was repopoulate the free text table. The recompile apparently does not do this. |
|
|
| |
Topic |
|
Topic Locked
