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

Question

Hello,

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 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.

Answer 1

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 varcahr(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_executesq (instead of EXEC) would be better.

Answer 2

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

Answer 3

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