Full text search CONTAINS predicate

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

Transact-SQL (2000)

Full text search CONTAINS predicate

Author

Topic

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-07-05 : 15:56:14

I'm using SQL Server's full text indexing and I keep coming across an error.
Here's my SQL:

CREATE Procedure SearchItem
(
    @Search nvarchar(255)
)

AS

SELECT
    id,
    name,
    cost

FROM

    table1

WHERE CONTAINS (name, @Search)

GO

This stored procedure runs fine and if I search something like book, for example, it does find it and display the results like I've formatted it. The problem, however, is when I try more words like book holder. The page error reads:

Syntax error occurred near 'holder'. Expected ''''' in search condition 'book holder'.

I've tried already placing the quotes like this:

WHERE CONTAINS (name, ' "@Search" ')

in this case, nothing is even display because I assume the @Search variable now becomes a string. So I'm really confused. I know you can also use FREETEXT but I need to use contains. Any ideas?

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-07-05 : 16:04:00

You might try something like this:

CREATE Procedure SearchItem
(
@Search nvarchar(255)
)

AS

SELECT
id,
name,
cost

FROM

table1
WHERE ISNULL(Name, '') LIKE CASE WHEN @Search <> '' THEN @Search
ELSE ISNULL(Name, '')
END

Does @Search contain wildcards? Or is it straight text?

Aj

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-07-05 : 16:08:27

It's straight text.

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-07-05 : 16:34:12

Your method doesn't seem to use the CONTAINS feature which is what I need. Is it even possible to use a variable with the CONTAINS in this manner?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-05 : 17:53:05

Does this work for you

CREATE Procedure SearchItem
(
    @Search nvarchar(255)
)

AS
SET @Search = '"' + @Search + '"'
SELECT
    id,
    name,
    cost

FROM

    table1

WHERE CONTAINS (name, @Search)

GO

Andy

Beauty is in the eyes of the beerholder

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-07-05 : 20:25:06

Perfect Andy! I had tried using the SET earlier but didn't write it like that. Thanks for your help and everyone else to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources