ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Filter Multiple Columns based on single criteria in SQL Server<p>Hi <a class="username" rel="ashraft1"> ashraft1</a>,</p> <p>Please refer below sample.</p> <p>For split string refer below link and create procedure refer below query.</p> <h1 class="header"><a title="Split and convert Comma Separated (Delimited) String to Table in SQL Server'a" href="https://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx">Split and convert Comma Separated (Delimited) String to Table in SQL Server</a></h1> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">CREATE 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,&#39;&#34;&#39;,&#39;&#39;&#39;&#39;); SET @sqlCommand =replace(@sqlCommand,&#39;&#34;&#39;,&#39;&#39;&#39;&#39;); SET @sqlCommand = @sqlCommand +&#39; where &#39; SET @where = &#39;&#39; DECLARE row_cursor CURSOR FOR SELECT Item FROM dbo.SplitString(@stringToFind, &#39; &#39;) OPEN row_cursor FETCH NEXT FROM row_cursor INTO @searchString WHILE @@FETCH_STATUS =0 BEGIN SET @flg=&#39;O&#39; &nbsp;&nbsp; &nbsp;SET @cursor = &#39;DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME &nbsp;&nbsp; &nbsp;FROM [&#39; + DB_NAME() + &#39;].INFORMATION_SCHEMA.COLUMNS &nbsp;&nbsp; &nbsp;WHERE TABLE_SCHEMA = &#39;&#39;&#39; + @schema + &#39;&#39;&#39; &nbsp;&nbsp; &nbsp;AND TABLE_NAME = &#39;&#39;&#39; + @table + &#39;&#39;&#39; &nbsp;&nbsp; &nbsp;AND DATA_TYPE IN (&#39;&#39;char&#39;&#39;,&#39;&#39;nchar&#39;&#39;,&#39;&#39;ntext&#39;&#39;,&#39;&#39;nvarchar&#39;&#39;,&#39;&#39;text&#39;&#39;,&#39;&#39;varchar&#39;&#39;) AND &nbsp;&nbsp; &nbsp;COLUMN_NAME IN (&#39;+ @SearchColumnName +&#39;)&#39; &nbsp;&nbsp; &nbsp;EXEC (@cursor) &nbsp;&nbsp; &nbsp;OPEN col_cursor &nbsp;&nbsp; &nbsp;FETCH NEXT FROM col_cursor INTO @columnName &nbsp;&nbsp; &nbsp;WHILE @@FETCH_STATUS = 0 &nbsp;&nbsp; &nbsp;BEGIN &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;IF @where &lt;&gt; &#39;&#39; AND @flg&lt;&gt;&#39;O&#39; &nbsp;&nbsp; &nbsp;BEGIN &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;SET @where = @where + &#39; OR&#39; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;END &nbsp;&nbsp;&nbsp; SET @flg =&#39;I&#39; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;SET @where = @where + &#39; [&#39; + @columnName + &#39;] LIKE &#39;&#39;%&#39; + @searchString + &#39;%&#39;&#39;&#39; &nbsp; &nbsp;&nbsp; &nbsp;FETCH NEXT FROM col_cursor INTO @columnName &nbsp;&nbsp; &nbsp;END &nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;CLOSE col_cursor &nbsp;&nbsp; &nbsp;DEALLOCATE col_cursor &nbsp;&nbsp; &nbsp;SET @where = @where + &#39;) AND (&#39; 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 + &#39;(&#39;+@where+&#39;&#39; EXEC (@sqlCommand) END TRY BEGIN CATCH IF CURSOR_STATUS(&#39;variable&#39;, &#39;col_cursor&#39;) &lt;&gt; -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH GO EXECUTE FilterMultipleColumns &#39;LP WoMen&#39;, dbo, TestDemoData, &#39;SELECT * FROM TestDemoData&#39;,&#39;&#39;&#39;Brand&#39;&#39;,&#39;&#39;Gender&#39;&#39;,&#39;&#39;Product&#39;&#39;&#39;</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <table border="1"> <tbody> <tr> <td>ID</td> <td>Brand</td> <td>Gender</td> <td>Product</td> <td>ProductType</td> <td>Size</td> </tr> <tr> <td>5</td> <td>LP</td> <td>Women</td> <td>Shirt</td> <td>Formal</td> <td>32</td> </tr> <tr> <td>6</td> <td>LP</td> <td>Women</td> <td>Pant</td> <td>Formal</td> <td>28</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/478284/Filter-Multiple-Columns-based-on-single-criteria-in-SQL-Server/https://www.aspforums.net:443/Threads/478284/Filter-Multiple-Columns-based-on-single-criteria-in-SQL-Server/Fri, 05 Apr 2019 06:19:27 GMTFilter Multiple Columns based on single criteria in SQL Server<p>Final Query.</p> <pre class="brush: 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,&#39;&#34;&#39;,&#39;&#39;&#39;&#39;); SET @sqlCommand =replace(@sqlCommand,&#39;&#34;&#39;,&#39;&#39;&#39;&#39;); SET @sqlCommand = @sqlCommand +&#39; where &#39; SET @where = &#39;&#39; DECLARE row_cursor CURSOR FOR SELECT item FROM dbo.SplitString(@stringToFind, &#39; &#39;) OPEN row_cursor FETCH NEXT FROM row_cursor INTO @searchString WHILE @@FETCH_STATUS =0 BEGIN SET @flg=&#39;O&#39; SET @cursor = &#39;DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM [&#39; + DB_NAME() + &#39;].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = &#39;&#39;&#39; + @schema + &#39;&#39;&#39; AND TABLE_NAME = &#39;&#39;&#39; + @table + &#39;&#39;&#39; AND DATA_TYPE IN (&#39;&#39;char&#39;&#39;,&#39;&#39;nchar&#39;&#39;,&#39;&#39;ntext&#39;&#39;,&#39;&#39;nvarchar&#39;&#39;,&#39;&#39;text&#39;&#39;,&#39;&#39;varchar&#39;&#39;,&#39;&#39;int&#39;&#39;,&#39;&#39;float&#39;&#39;) AND COLUMN_NAME IN (&#39;+ @SearchColumnName +&#39;)&#39; EXEC (@cursor) OPEN col_cursor FETCH NEXT FROM col_cursor INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN IF @where &lt;&gt; &#39;&#39; AND @flg&lt;&gt;&#39;O&#39; BEGIN SET @where = @where + &#39; OR&#39; END SET @flg =&#39;I&#39; SET @where = @where + &#39; [&#39; + @columnName + &#39;] LIKE &#39;&#39;%&#39; + @searchString + &#39;%&#39;&#39;&#39; FETCH NEXT FROM col_cursor INTO @columnName END CLOSE col_cursor DEALLOCATE col_cursor SET @where = @where + &#39;) AND (&#39; 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 + &#39;(&#39;+@where+&#39;&#39; EXEC (@sqlCommand) END TRY BEGIN CATCH IF CURSOR_STATUS(&#39;variable&#39;, &#39;col_cursor&#39;) &lt;&gt; -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH GO EXECUTE FilterMultipleColumns &#39;Women 28&#39;, dbo, Test, &#39;SELECT * from [dbo].[Test]&#39;,&#39;&#39;&#39;Brand&#39;&#39;,&#39;&#39;Gender&#39;&#39;,&#39;&#39;Product&#39;&#39;,&#39;&#39;ProductType&#39;&#39;,&#39;&#39;Size&#39;&#39;&#39; </pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/478284/Filter-Multiple-Columns-based-on-single-criteria-in-SQL-Server/https://www.aspforums.net:443/Threads/478284/Filter-Multiple-Columns-based-on-single-criteria-in-SQL-Server/Sat, 06 Apr 2019 03:56:30 GMT