FullText Search implementation on SQL server 2008 Problem

I have the following Stored proc开发者_StackOverflow中文版edure,

CREATE Procedure sp_SearchDB (
  @SearchWord nvarchar(200)
)

SET @SearchWord = '"*' + @SearchWord + '*"'

 Select * 
   from Table1
  Where Contains(Description, @SearchWord)

When I search "SQL Server", it returns me results with "SQL" or "Server", but it will not return data with "SQLServer"(if there is no white space), similarly if give searchword=SQLServer, it will not return "SQL Server"(with white space). How can I get both results "SQL Server" and "SQLServer"?

You need to execute the procedure as follows:

EXEC sp_SearchDB N'SQL SERVER'

Bad news, you can do it, but it will give you some false positives. Try:

EXEC sp_SearchDB N'SQL'

With you do care about false positives, them the answer is: there is no way of doing that. If you want both "SQL Server" and "SQLServer" you should do:

Select * from Table1 Where Contains(Description, '"SQL Server" OR "SQLServer"').

You can write a code that will always try do find for both words together and separated. If your customer searches for:

baby sitter

Your code will create this variations when it finds a white space:

'"baby sitter" or "babysitter"' 

I hope you get it.