Use CASE to conditionally add an AND condition to query

Question

I am trying to create a stored procedure where, depending on a parameter not being an empty string, an AND condition should be added to the query. If the parameter is an empty string, the AND condition should not be added at all. I want the matching to use LIKE not "=", as in:

DECLARE @searchstring VARCHAR(50);

SET @searchstring = 'hang up';

SELECT *
FROM ContactLog
WHERE CREATED_DATE > '2020-01-01'
    AND Brief_Statement LIKE '%' + CASE 
        WHEN @searchstring != ''
            THEN @searchstring
        ELSE Brief_Statement
        END + '%'
    AND Log_Type = 'L';

SET @searchstring = '';

SELECT *
FROM ContactLog
WHERE CREATED_DATE > '2020-01-01'
    -- in this case, since @searchString is empty, I'd like to eliminate this next condition altogether
    AND Brief_Statement LIKE '%' + CASE 
        WHEN @searchstring != ''
            THEN @searchstring
        ELSE Brief_Statement
        END + '%'
    AND Log_Type = 'L'

I know that if I am using "=" instead of LIKE, I can do this...

SET @searchstring = '';

SELECT TOP 10 *
FROM ContactLog
WHERE CREATED_DATE > '2020-01-01'
    AND Brief_Statement = CASE 
        WHEN @searchstring != ''
            THEN @searchstring
        ELSE Brief_Statement
        END
    AND Log_Type = 'L'

But I am not sure of the effect of doing this with LIKE and would like the query to be as efficient as possible.

What is a good way to do this?

https://dba.stackexchange.com/a/74466/2131 Don't try and make a single query work for all cases as it can suck. Instead, make the where clause dynamic to reflect the different search possibilities. Also, be wary of SQL Injection — billinkc, Jan 31 '20 at 17:05
As you are searching for a substring (with a leading wildcard) it won't make much difference to performance. Either you are returning all rows matching the other predicates anyway or you at least need to examine them to see if they match the search string predicate. It is not going to make a difference to rows read — Martin Smith, Jan 31 '20 at 17:09
And depending on the size, structure of `ContacLog`, and ultimately what you're doing with the data, you might be better served by dumping intermediate results to a temporary table and then applying the leading wildcard search. Testing will bear out whether subsetting the data will be more efficient by you or the database engine — billinkc, Jan 31 '20 at 17:13

Suraj Kumar · Accepted Answer · 2020-01-31T17:22:59.043

You can try this conditional where clause. I have simplified your case statement to which was supplied in the filter condition.

Sample data would be more efficient while applying as you said you can try with the like operator in place of equal to operator.

Here is an additional reference to you.

SELECT TOP 10 *
FROM ContactLog
WHERE CREATED_DATE > '2020-01-01'
    AND @searchstring = ''
    OR (
        @searchstring <> ''
        AND Brief_Statement = @searchstring
        )
    AND Log_Type = 'L'

Here is the demo

Create table TestTable (Id int, Name varchar(10))
Insert into TestTable Values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')

Select * from TestTable --Selecting all the records.

declare @searchstring varchar(5) = ''; -- Without filter criteria passing.
Select * from TestTable
where 1 = 1
AND @searchstring = ''
    OR (
        @searchstring <> ''
        AND Name = @searchstring
        )

set  @searchstring = 'D' --By passing filter criteria.
Select * from TestTable
where 1 = 1
AND @searchstring = ''
    OR (
        @searchstring <> ''
        AND Name = @searchstring
        )

Demo Link

I ended up using this based on your answer: and (@searchstring = '' or (@searchstring != '' and Brief_Statement like + '%' + @searchstring + '%')) Thanks — Phil, Jan 31 '20 at 17:29

Use CASE to conditionally add an AND condition to query

1 Answers1