Creating a stored procedure in SQL Server 2008 that will do a "facebook search"

I'm trying to implement a facebook search in my system (auto suggest while typing). I've managed to code all the ajax stuff, but I'm not sure how to query the database.

I've created a table called People which contains the fields: ID, FirstName, LastName, MiddleName, Email. I've also created a FTS-index on all those fields.

I want to create a stored procedure that will get as a parameter开发者_Go百科 the text inserted in the query box and returns the suggestions.

For example, When I will write in the textbox the query "Joh Do" It will translate to the query:

select * from People where contains(*, '"Joh*"') and contains(*, '"Do*"')

Is there a way to do that in stored procedure?

P.S I've tried to use the syntax

select * from People where contains(*,'"Joh*" and "Do*"')

but it didn't returned the expected results, probably because it needs to search the words on different fields. Is there a way to fix that?

Thanks.

Try

select   *
from     People
where    (FirstName  Like '%'+ @FirstName  + '%') and
         (MiddleName Like '%'+ @MiddleName + '%') and
         (LastName   Like '%'+ @LastName   + '%')

Also you may want to restrict the results to only return a maximum of say 10 by using:

select top 10

EDIT 1:

OK I now understand the problem better. I would use dynamic sql thus:

First create a split function e.g. Example Split function using XML trick

Then use dynamic sql:

declare @tstr varchar (500)

set @tstr = ''

select  @tstr =@tstr + ' Contains(*, ''"'+ val + '*")' + ' and '
from  dbo.split(@SearchStr, ' ') 


set @tstr =  left(@tstr,len(@tstr)-4)

select @tstr
Declare @dsql as varchar(500)

set @dsql = 'select * from People where '+ @tstr

exec (@dsql)

Also please note as per Remus, be aware of SQL Injections, the use of sp_executesql (instead of EXEC) would be better.

The problem is the open list nature of the argument. I can be Joh, it can be Joh Do, it can be Joh Do Na and so on and so forth. You have two main alternatives:

• parse the input in the web app (in ASP I assume) and then call an appropriate procedure for the number of entries (ie. exec usp_findSuggestions1 'Joh', exec usp_findSuggestions2 'Joh', 'Do', exec usp_findSuggestions1 'Joh', 'Do', 'Na'). The first procedure uses 1 contains, the second has 2 contains .. and contains ... and the last has 3. This may look totally ugly from a DRY, code design and specially code maintenance pov, but is actually the best solution as far as T-SQL is concerned, due primarily to the plan stability of these queries.
• pass the input straight into a single stored procedure, where you can split it into components and build a dynamic T-SQL query with as many contains as necessary.

Both solutions are imperfect. Ultimately, you have two problems, and both have been investigated before to quite some depth:

• the problem of passing a list to a T-SQL procedure. See Arrays and Lists in SQL Server 2005 and Beyond
• the problem of an undetermined number of conditions in the WHERE clause, see The Curse and Blessings of Dynamic SQL

The AJAX Toolkit has the "AutoComplete" control that provides this functionality out of the box. It is very simple to use.

Look at a sample here