Using IsAbout in Full-Text Search

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 2000 Forums

SQL Server Development (2000)

Using IsAbout in Full-Text Search

Author

Topic

GoldenCupChamp
Starting Member

2 Posts

Posted - 2004-07-22 : 10:50:31

I'm a rookie at SQL and SP's, but here goes...

I'm trying to use Full-Text Search to rank online searches of my products on my site. The user enters 2 keywords for a search. The search results are ordered by descending rank.

Every example I've seen (Microsoft) using ISABOUT hard codes the keywords that are used in the search. My SP works if I do in fact hard code the search terms, but I obviously want the keywords to be variables (@Keyword and @Keyword2).

What is the syntax for using @Keyword in the ISABOUT statement?

Here is what I have so far...

-------------------------------------------------------
@Keyword varchar(256) = NULL,
@Keyword2 varchar(256) = NULL

SELECT t.Rank, P.* FROM tbProducts P
INNER JOIN containstable(tbProducts, ProductName, 'ISABOUT(@Keyword weight (.8), @Keyword2 weight (.7))') t ON P.ProductID = t.[key]
ORDER BY t.Rank DESC
-------------------------------------------------------

Thanks,

GoldenCupChamp

samtoffa
Yak Posting Veteran

60 Posts

Posted - 2004-07-26 : 07:42:42

Can you post some test data and the full sp?
Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-26 : 09:25:43

Hi Champ,

You'll need to concatenate the keyword with the search condition in a variable and pass that variable to the CONTAINSTABLE function as a parameter. That's the only way it'll work.


@Keyword varchar(256) = NULL,
@Keyword2 varchar(256) = NULL

DECLARE @searchCondition varchar(1000)

SET @searchCondition = 'ISABOUT(' + @Keyword + ' weight (.8), ' + @Keyword2 + ' weight (.7))'

SELECT t.Rank, P.* FROM tbProducts P
INNER JOIN containstable(tbProducts, ProductName, @searchCondition) t ON P.ProductID = t.[key]
ORDER BY t.Rank DESC

GoldenCupChamp
Starting Member

2 Posts

Posted - 2004-07-26 : 10:00:40

mohdowais,

Yes! It works great, thanks. I added a check for a NULL @Keyword2 variable to the SET statement. @Keyword1 doesn't need the check because there will always be at least one keyword supplied by the user...

--------------------------------------------
@Keyword varchar(256) = NULL,
@Keyword2 varchar(256) = NULL

DECLARE @searchCondition varchar(1000)

SET @SearchCondition = 'isabout('+ @Keyword + ' weight(.9)' + CASE WHEN @Keyword2 IS NULL THEN '' ELSE ', ' + @Keyword2 + ' weight(.9)' END + ')'

SELECT t.Rank, P.* FROM tbProducts P
INNER JOIN containstable(tbProducts, ProductName, @searchCondition) t ON P.ProductID = t.[key]
ORDER BY t.Rank DESC
--------------------------------------------

GoldenCupChamp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources