Final Query.
Alter PROCEDURE FilterMultipleColumns
@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 item FROM dbo.SplitString(@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'',''int'',''float'') 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
EXECUTE FilterMultipleColumns 'Women 28', dbo, Test, 'SELECT * from [dbo].[Test]','''Brand'',''Gender'',''Product'',''ProductType'',''Size'''
