Full Text Paramter in Stored Procedure

Question

Full Text Paramter in Stored Procedure

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
November 8, 2002 at 7:58 am

#101301

Morning all!
If I execute the following sp:
CREATE PROCEDURE procNotesTextSearch
AS
SET NOCOUNT ON
SELECT *
FROM notescustlog INNER JOIN custlog ON notes_key = [Key]
WHERE notes_key IN
(SELECT DISTINCT notes_key FROM notescustlog WHERE CONTAINS(notes_text, ' damage '))
I get hundreds of rows returned.
If I execute the following:
CREATE PROCEDURE procNotesTextSearch @strCriteria AS varchar(100)
AS
SET NOCOUNT ON
SELECT *
FROM notescustlog INNER JOIN custlog ON notes_key = [Key]
WHERE notes_key IN
(SELECT DISTINCT notes_key FROM notescustlog WHERE CONTAINS(notes_text, ' @strCriteria '))
With EXECUTE procNotesTextSearch @strCriteria = 'damage'
I get no rows.
Isn't possible to pass a parameter into the CONTAINS clause?
Thanks
Jonathan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

You have put @strCriteria in quotes, so it is searching for the literal '@strCriteria', not the contents of the variable. Just remove the quotes:-

(SELECT DISTINCT notes_key FROM notescustlog WHERE CONTAINS(notes_text, @strCriteria))

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

CONTAINS cannot use variables for the search condition.

You would need to build the string and execute it.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 3

Thanks ianscarlett and simonsabin! After much frustration I am ready to fully accept Simon's position. It ain't gonna happen with a variable!

Jonathan

lambje SSCrazy Points: 2055 More actions · Answer 4

Books Online contradicts Simon's claim:

H. Use CONTAINS with variables

This example uses a variable instead of a specific search term.

USE pubs

GO

DECLARE @SearchWord varchar(30)

SET @SearchWord ='Moon'

SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

This is direct from BOL

Specifies some text to search for in column. Variables cannot be used for the search condition.

looks like a contradiction in BOL

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

lambje SSCrazy Points: 2055 More actions · Answer 6

Simon -- No surprise about BOL

I'm curious where you found your reference. Mine was simply going to the index, typing CONTAINS and scrolling to the bottom of that function.

I did play with the function a bit and couldn't get it to work even using a string. I typically send in parameters to procs and do something like:

SELECT * FROM table WHERE column LIKE '%' + @parm + '%'

(hmmm, for some reason the pluses between my %'s and parm don't show up in the preview)

Have you used CONTAINS? Does it actually work?

richardd Hall of Fame Points: 3963 More actions · Answer 7

The information on CONTAINS was updated in the Sept. 2001 update to BOL. This may have been one of the updates. I have successfully used CONTAINS with a variable in several applications.

Updated BOL:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 8

lambje

did the same as you, but didn't look at the example, reference is about 1/3 or the way down.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons