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!

Corbett

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:

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

Clairvoyance

Answer 2

Just declare argument 2 of contains as nvarchar(4000) instead of nvarchar(max) and it will work.

See the difference here: https://msdn.microsoft.com/en-us/library/ms186939.aspx

2 GB is a bit too much for the search expression.

Zoroaster

Answer 3

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.

Corbett