I inherited some code that searches for a string across multiple columns in a table. My predecessor used dynamic SQL to do this, like so:

SET @tmpSQL9a = @tmpSQL9a  
'and ( '   CHAR(13)  

'           charindex('''   @parDashboardFilter_Search   ''',coalesce(h.RegionName,'''')) > 0  '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(h.City,'''')) > 0  '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(h.[StAte],'''')) > 0  '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(h.Zip,'''')) > 0  '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(h.PhoneNumber,'''')) > 0  '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(ca.FirstName,'''')) > 0   '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(ca.LastName,'''')) > 0   '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(ca.PrimaryPHone,'''')) > 0   '   CHAR(13)  
'           or  charindex('''   @parDashboardFilter_Search   ''',coalesce(ca.SecondaryPhone,'''')) > 0   '   CHAR(13)  

This is a small sample: in this one instance, there are about 40 more lines of criteria written just as you see above. I have to imagine that there's a better way, but googling hasn't helped and my brain is fried.

Any advice?

CodePudding user response：

One method would as your predecessor did, however, without the SQL injection; which is a massive security vulnerability.

An alternative would be to use an EXISTS with a subquery that unpivots the columns, something like this:

WHERE EXISTS (SELECT 1
              FROM(VALUES(h.RegionName),
                         (h.City), --Obviously list all your columns, not just these 3
                         (h.State)) V(SearchString)
              WHERE V.SearchString LIKE '%'   @parDashboardFilter_Search    '%')

CodePudding user response：

And for completeness, one more option via XML/XQuery.

It is checking if parameter's value belongs to a sequence of values for all columns in a row.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, City VARCHAR(20), State CHAR(2), Zip VARCHAR(20));
INSERT @tbl (City, State, Zip) VALUES
('Miami', 'FL', '33160'),
('Dallas', 'TX', '15098'),
('Los Angeles', 'CA', '45660');
-- DDL and sample data population, end

DECLARE @par_Search VARCHAR(20) = 'TX';

SELECT t.*, x
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE) AS t1(x)
WHERE x.exist('/r/*[text() = sql:variable("@par_Search")]') = 1;

Output

CodePudding user response：

Just another little option where you don't have to list all the columns..

Select A.*
  From YourTable A
  Where (Select A.* for JSON Path) like '%MySearchString%'

Note: If you have (for example) a column named PHONE and you search 'PHONE' you will get all rows where PHONE is not null