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

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.

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.