Creating a stored procedure with CONTAINS in SQL Server 2008

Question

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

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

and the number of keywords can vary, so I tried to pass the entire FORMSOF ... AND FORMSOF ..... sentence as a parameter, declaring the parameter as nvarchar (max), but it won’t let me do this by saying that the type of argument "nvarchar (max)" is not valid for argument 2 of "CONTAINS".

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

Answer 1

You can build it dynamically and pass keywords as parameters. Running with sp_executesql allows you to use the query cache plan as described in the answers to this question:

Alternative to running dynamic SQL

You may need to keep an eye on this issue, which is related to sniffing options and full text queries:

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

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 too much for search expression.

Answer 3

this seems silly, but using nvarchar (500) instead of nvarchar (max), Sql Server carefully accepts it and works great. Still trying to get some idea of ​​sp_executesql, thanks.