One time I was writing a dynamic search where the user could choose to search multiple fields. Typically you build a sql statement and add the search criteria in the where clause based on whatever fields the user is searching in. Well, that was my first attempt. Reality whacked me in the face when I dicovered that the users don't have direct table access permissions; everything has to be done through a stored procedure.

Now, I perceive a sort of religious war between people who use dynamic sql with EXEC statements in stored procs and those who do not believe in it. I fall into the latter camp. So out of stubborness, I set out to create a dynamic query that didn't use SQL EXEC. This is what I came up with.

First, the SP takes a @keywords parameter that contains comma-separated search terms (this is all AND searches). Then I have a bunch of flag parameters that indicate which fields to search in. The SP uses a function that I wrote and love called CSVToTable. It splits CSV values into a table that you can join to.

ALTER FUNCTION [dbo].[fn_CSVToTable] (@CSVList varchar(8000),@Delimiter char(1))  
RETURNS @csvtable table (val varchar(1000))
AS  
BEGIN 
-- variables for position marking
declare @separatorposition int, @arrayvalue varchar(1000)
-- Pad the list if needed
if substring(rtrim(@csvlist),len(rtrim(@csvlist)),1)<>@Delimiter
	set @csvlist = @csvlist + @Delimiter
-- Loop through string
while patindex('%'+@Delimiter+'%', @csvlist) <> 0
begin
	select @separatorPosition = patindex('%'+@Delimiter+'%', @csvlist) 
	select @arrayValue = left(@csvlist, @separatorPosition - 1)
	INSERT into @csvtable(val) values (rtrim(ltrim(@arrayValue)))
	
	select @csvlist = stuff(@csvlist,1,@separatorPosition, '')
end 
-- return table
return 
END

The magic happens when you join to the table of keywords. You do a join using a substring match while making sure the flag is set. Here's the query that can easily be converted to a stored proc. This is using Northwind, BTW.

declare @keywords varchar(2000),
@SearchFirstName bit,
@SearchLastName bit,
@SearchTitle bit,
@SearchAddress bit,
@SearchCity bit,
@SearchRegion bit,
@SearchCountry bit,
@SearchNotes bit

declare @t table(keyword varchar(50))

set @keywords='sales,full'
set @searchtitle=1
set @searchlastname=1

-- put the keywords into a table
insert @t 
select * from fn_CSVToTable(@keywords,',')

-- query the items
select employeeid,title,lastname,firstname,address,city,region,postalcode,country
from (
	select t.keyword,employeeid,title,lastname,firstname,address,city,region,postalcode,country
	from Employees e 
	join @t t on (@searchFirstName<>0 and patindex('%'+keyword+'%',e.FirstName)>0)
			or (@searchLastName<>0 and patindex('%'+keyword+'%',e.LastName)>0)
			or (@searchTitle<>0 and patindex('%'+keyword+'%',e.Title)>0)
			or (@searchAddress<>0  and patindex('%'+keyword+'%',e.Address)>0)
			or (@searchCity<>0  and patindex('%'+keyword+'%',e.city)>0)
			or (@searchRegion<>0  and patindex('%'+keyword+'%',e.region)>0)
			or (@searchCountry<>0  and patindex('%'+keyword+'%',e.Country)>0)
			or (@searchNotes<>0  and patindex('%'+keyword+'%',e.Notes)>0)
	) r
group by employeeid,title,lastname,firstname,address,city,region,postalcode,country
having count(keyword)>=(select count(*) from @t)-- we must match all the keywords
order by employeeid

So what this does is joins a row from the keyword temporary table for each searched row that matches. You may match one, two, or more rows, so your temporary result set will be at most: # of rows in searched query * # of keywords searched. File that under memory considerations if you want to implement this technique. Once that is built, it is filtered using the HAVING to make sure that all the keywords were matched somewhere.

What else can you do with this query? How about ranked results? Try changing the query to:

select count(keyword)/cast((select count(*) from @t) as float),employeeid,title,lastname,firstname,
address,city,region,postalcode,country
from (
	select t.keyword,employeeid,title,lastname,firstname,address,city,region,postalcode,country
	from Employees e 
	join @t t on (@searchFirstName<>0 and patindex('%'+keyword+'%',e.FirstName)>0)
			or (@searchLastName<>0 and patindex('%'+keyword+'%',e.LastName)>0)
			or (@searchTitle<>0 and patindex('%'+keyword+'%',e.Title)>0)
			or (@searchAddress<>0  and patindex('%'+keyword+'%',e.Address)>0)
			or (@searchCity<>0  and patindex('%'+keyword+'%',e.city)>0)
			or (@searchRegion<>0  and patindex('%'+keyword+'%',e.region)>0)
			or (@searchCountry<>0  and patindex('%'+keyword+'%',e.Country)>0)
			or (@searchNotes<>0  and patindex('%'+keyword+'%',e.Notes)>0)
	) r
group by employeeid,title,lastname,firstname,address,city,region,postalcode,country
order by count(keyword) desc

Now you have ranked matches with the percent matched.

That's really all I wanted to put out there - just a little expression of stubborness and probably a disregard of performance to illustrate a point