Filter Multiple Columns based on multiple search criteria in SQL Server

Last Reply 7 days ago By pandeyism

Posted 7 days ago

Filter Multiple Columns based on single criteria in SQL Server

EXECUTE FilterMultipleColumns 'Women 28', dbo, Test, 'SELECT * from [dbo].[Test]','''Brand'',''Gender'',''Product'',''ProductType'',''Size'''

Along with the above all column filter i would to add each column secific filter as well like 

SELECT * from [dbo].[Test] where Brand='' and Gender='' and Product='' and ProdcutType='' and Size=''
Posted 7 days ago Modified on 6 days ago

Hi ashraft1,

Refer below sql query.

SQl

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+'AND '+'('+@where+'' 
SET @sqlCommand = @sqlCommand +' '
   
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'') 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+ 'AND' +'('+@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
DECLARE @A VARCHAR(50)
DECLARE @B VARCHAR(50)
SET @A = 'Shirt'
DECLARE @Command VARCHAR(MAX)
SET @Command = 'SELECT * FROM TestDemoData WHERE Product = '''+@A+''''
EXECUTE FilterMultipleColumns 'LP WoMen', dbo, TestDemoData,@Command ,'''Brand'',''Gender'',''Product'''

Output

ID Brand Gender Product ProductType Size
5 LP Women Shirt Formal 32