Find multiple strings in selected columns

Introduction

Recently I was come through with a problem where I wanted to apply a filter on a table but conditions are a little different from a simple 'like' query. 

Background 

Let us try to study the condition. Let's say we have a table named 'ContactDetails' with the following columns and records:

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com
Natasha         Williams         natasha@gmail.com
Gary            James            gary@live.com

Now the user puts string 'gary gmail' for search; in this condition a SQL stored procedure should return the following values only:

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com

because only gary and gmail exist in the records in any column. 

Using the code

For performing this task we have to create a function and a stored procedure over SQL Server. 

1. Split the searchable string with specific deliminator.
2. Create the appropriate Select SQL for searching those strings in the desired columns.

So, here is the first function which I use for splitting the string with a specific deliminator.

--
-- Here First @Sep variable use for separator and @s use for string which need to split   

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))

RETURNS @t TABLE
    (
        val VARCHAR(MAX)
    )   
AS
    BEGIN
        DECLARE @xml XML
        SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

        INSERT INTO @t(val)
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM @xml.nodes('//root/r') AS RECORDS(r)

        RETURN
    END
GO

For getting the result from it we can simply call it using:

SELECT * FROM dbo.Split (' ', 'gary gmail')

The result of this will be:

gary
gmail

Now we use this function over our main stored procedure:

-- Procedure for find the string in table
--@stringToFind  'string which need to find'
--@schema  'schema name' e.g. dbo
--@tableName  'table name'
--@SqlCommand  'sql command' 
--@SearchColumnName  'columns name; where string need to find'

CREATE PROCEDURE [dbo].[sp_FindMultipleStringInTable] 
@stringToFind VARCHAR(MAX), 
@schema sysname, 
@table sysname ,
@sqlCommand VARCHAR(MAX), 
@SearchColumnName VARCHAR(MAX)
AS
DECLARE @where VARCHAR(MAX)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(MAX)
DECLARE @searchString VARCHAR(MAX)
DECLARE @flg CHAR(1)
BEGIN TRY

SET @SearchColumnName =replace(@SearchColumnName,'"','''');
SET @sqlCommand =replace(@sqlCommand,'"','''');

SET @sqlCommand = @sqlCommand +' where '

SET @where = ''

DECLARE row_cursor CURSOR FOR 
SELECT * FROM dbo.split (' ',@stringToFind)

OPEN row_cursor 
FETCH NEXT FROM row_cursor INTO @searchString

WHILE @@FETCH_STATUS =0 
BEGIN 

SET @flg='O'
	SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
	FROM [' + DB_NAME() + '].INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_SCHEMA = ''' + @schema + '''
	AND TABLE_NAME = ''' + @table + '''
	AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'') AND
	COLUMN_NAME IN ('+ @SearchColumnName +')'
	EXEC (@cursor)

	OPEN col_cursor
	FETCH NEXT FROM col_cursor INTO @columnName

	WHILE @@FETCH_STATUS = 0
	BEGIN
   
	IF @where <> '' AND @flg<>'O'
	BEGIN
		SET @where = @where + ' OR'  				
	END 
    SET @flg ='I'
   
	SET @where = @where + ' [' + @columnName + '] LIKE ''%' + @searchString + '%'''
  
	FETCH NEXT FROM col_cursor INTO @columnName
	END
	
	CLOSE col_cursor
	DEALLOCATE col_cursor
	SET @where = @where + ') AND ('
FETCH NEXT FROM row_cursor INTO @searchString
END

CLOSE row_cursor
DEALLOCATE row_cursor

SET @where =SUBSTRING(@where,0,(LEN(@Where)-5))
SET @sqlCommand = @sqlCommand + '('+@where+''

EXEC (@sqlCommand)


END TRY
BEGIN CATCH
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
GO

After creating this stored procedure, we have to call it like:

EXECUTE dbo.sp_FindMultipleStringInTable 'gary gmail', dbo, 
contactdetails, 'Select * from contactdetails', 
'''FirstName'',''LastName'',''Email'''

The result will be:

FirstName       LastName         Email
Gary            Lewis            gary@gmail.com