Simple Search String Stored Procedure?

[DaveL]

I have a simple search string being passed from a form that can contain multiple words separated by spaces.... "word1 word2 word3".

Can anyone give me ideas on the simplest way to create a stored procedure that will select any records in a table (table1) where a specific column (column1) contains any of those words?

Thanks!

 

[DBCustard]

To be honest this is not easy, the main problem is that an SP cannot take an undetermined number of arguments, if you settle for passing to the SP a comma seperated list you could use the following stored procedure:

Note, you'll have to modify the select statement, I've created it, to look at sysdatabases, to demonstrate how to solve the porblem, there is also an example of how to cal it below ...

Also beware of creating a query where the only constraint is a like, especially if you have to use or statements, or statements generally perform badly, because at best they result in index scans. The like %value% will also degrade performance.

Hope this Helps,
Darren

create procedure dbt1(@words as varchar(8000)) as
declare @nextword varchar(255)
declare @wrkWords varchar(8000)
declare @mySQL varchar(1000)
declare @myCondition varchar(255)

set @mySQL = 'select * from master..sysdatabases where'

set @wrkWords = @words

while len(@wrkWords) >0
begin
if charindex(',', @wrkWords) > 0
begin
set @nextword = left(@wrkWords, charindex(',', @wrkWords) -1)
set @wrkWords = right (@wrkWords, len(@wrkWords) - charindex(',', @wrkWords))
set @myCondition = ' name like ''%' + @nextWord + '%'' or'
end
else
begin
set @nextword = @wrkWords
set @wrkWords = ''
set @myCondition = ' name like ''%' + @nextWord + '%'''
end
set @mySQL = @mySQL + @myCondition

end

exec (@mySQL)
go


exec dbt1 'mas,temp,del'