SQL Keyword Search to Return Any Results

Question

I have created a keyword search in SQL that takes a list of keywords and checks against various columns in the product database. So you can search the title, description, etc.

I'd like it to return all results and skip results where there is nothing returned.

It works when all of the keywords are a match, but it does not work when one or more keywords returns no results. I am thinking there needs to be some check for zero results or a better way to match the temp table against the product table.

In the example below, if I search for "braun,washer" I get two results.

ProductId      ProductName
23             Large Braun Washer
45             Small Washer by Braun

However, I get no results if I search for "braun,washer,washing," because none of the products contain the word "washing." I still want to get the two results for the first two hits.

--temp table for keyword values
create table #delimitedKeywords
(
    [keywordID] int identity(1,1),
    keywords varchar(100)
)   
declare @keywordValues varchar(50);
declare @arrayLength int;
declare @position int;
declare @nextDelim int;
declare @prevDelim int;
declare @delimValue varchar(10);
set @keywordValues = 'braun,washer,washing,';
set @arrayLength = LEN(@keywordValues);
set @position = 1;
set @nextDelim = 0;
set @prevDelim = 0;

--loop through position
while @position <= @arrayLength 
    begin
        --substring comma delimeter
        set @nextDelim = CHARINDEX(',', @keywordValues, @position);
        set @delimValue = (SUBSTRING(@keywordValues, @position, @nextDelim - @prevDelim -1));

        --stop loop if at end   
        if LEN(@delimValue) > 0
        BEGIN
            insert into #delimitedKeywords
                (keywords)
            values
                (@delimValue);
        END
        set @prevDelim = @nextDelim;
        set @position= @nextDelim+1;
    end

begin

--select the keywords from the temp table to search on
declare @Keyword varchar(100)

select @Keyword =  [keywords] from #delimitedKeywords

--search product table
SELECT [ProductId]
      ,[ProductName]
      ,[ProductDescription]
      ,[ProductBrief]
      ,[ProductSpecification]

  FROM [Product]

  WHERE 
  [ProductName] like '%' + @Keyword + '%'
  OR [ProductDescription] like '%' + @Keyword + '%'
  OR [ProductSpecification] like '%' + @Keyword + '%'
  OR [ProductBrief] like '%' + @Keyword + '%'

--drop temp
drop table #delimitedKeywords;
end

The client passes a single SQL parameter to a stored procedure in MS SQL. SQL then splits the parameter on a comma delimiter, and then creates a temporary table to search against.

Eventually I plan to weight the results, so that results from the title are more than those elsewhere.

Answer 1

Isn't this going to return a single value:

select @Keyword =  [keywords] from #delimitedKeywords

... maybe something like this:

SELECT DISTINCT [ProductId]
  ,[ProductName]
  ,[ProductDescription]
  ,[ProductBrief]
  ,[ProductSpecification]
FROM [Product]
INNER JOIN #delimitedKeywords
ON [ProductName] like '%' + Keywords + '%'
OR [ProductDescription] like '%' + Keywords + '%'
OR [ProductSpecification] like '%' + Keywords + '%'
OR [ProductBrief] like '%' + Keywords + '%'