Using Contains in Stored Procedures 1

[kandym]

I'd like to create a stored procedure for a full-text search query using the "contains" function, with a generation term. It would look something like:

CREATE PROCEDURE [search] @string varchar AS
select t.item FROM table t
where contains(t.info, ' FORMSOF (INFLECTIONAL, @string) ')

Using this, I could search the table for the word "apples" by calling search @string = 'Apples'. The problem is, it won't recognize the @string parameter in the contains statement - it's actually searching the table for "@string".

So, is there any good way to get a parameter into the generation term of the contains function?

 

[Tim1]

Hiya,

I think that you are going to need to generate the entire contains statement as one long variable:

CREATE PROCEDURE [search] @string varchar ,@cntnstmt VARCHAR(255)
AS
SELECT @cntnstmt = "FORMSOF (INFLECTIONAL,'"
SELECT @cntnstmt = @cntnstmt + @string
SELECT @cntnstmt = @cntnstmt = &quot;'&quot; <---- There is the single quote between two
double quotes

SELECT t.item
FROM table t
WHERE CONTAINS (t.info,@cntnstmt)