Best way to implement a stored procedure with full text search

Question

I would like to run a search with MSSQL Full text engine where given the following user input: "Hollywood square"

I want the results to have both Hollywood and square[s] in them.

I can create a method on the web server (C#, ASP.NET) to dynamically produce a sql statement like this:

SELECT TITLE
FROM MOVIES
WHERE CONTAINS(TITLE,'"hollywood*"')
AND CONTAINS(TITLE, '"square*"')

Easy enough. HOWEVER, I would like this in a stored procedure for added speed benefit and security for adding parameters.

Can I have my cake and eat it too?

Answer 1

The last time I had to do this (with MSSQL Server 2005) I ended up moving the whole search functionality over to Lucene (the Java version, though Lucene.Net now exists I believe). I had high hopes of the full text search but this specific problem annoyed me so much I gave up.

Answer 2

Have you tried using the AND logical operator in your string? I pass in a raw string to my sproc and stuff 'AND' between the words.

http://msdn.microsoft.com/en-us/library/ms187787.aspx

Answer 3

I agreed with above, look into AND clauses

SELECT TITLE
FROM MOVIES
WHERE CONTAINS(TITLE,'"hollywood*" AND "square*"')

However you shouldn't have to split the input sentences, you can use variable

SELECT TITLE
FROM MOVIES
WHERE CONTAINS(TITLE,@parameter)

by the way search for the exact term (contains) search for any term in the phrase (freetext)