Create stored procedure with CONTAINS in SQL Server 2008

Question

I want to create a stored procedure to do some combined keyword search using CONTAINS,something like below:

SELECT theContent
FROM FtsTest
WHERE CONTAINS
   (theContent, 
   ' FORMSOF (INFLECTIONAL, keyword1) AND FORMSOF (INFLECTIONAL, keyword2)');

and he number of keywords may vary, so I tried to pass the whole 'FORMSOF... AND FORMSOF.....'clause as a parameter,declaring the parameter as nvarchar(max),but it won't let me do it,saying The argument type "nvarchar(max)" is invalid for argument 2 of "CONTAINS".

So, is there any way to make it work with the sp? Thanks!

Answer 1

You could build it dynamically and pass in the keywords as parameters. Executing with sp_executesql allows you to take advantage of the query plan cache, as described in the answers to this question:

http://stackoverflow.com/questions/1012743/alternative-to-executing-dynamic-sql

You may need to watch out for this issue, though, that relates to parameter sniffing and full text queries:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=510118

Answer 2

this seems stupid,but using nvarchar(500) instead of nvarchar(max), Sql Server cheerfully accepts it and works just fine. Still trying to gain some insight on sp_executesql,thanks.