Using of CASE in WHERE clause to evaluate empty parameters

Question

Is there a better way to obtain the following?

DECLARE @Desc VARCHAR(200) = ''

SELECT [id],
       [Desc],
       [Col1],
       [Col2]
FROM [dbo].[tbl]
WHERE [Desc] LIKE CASE
                WHEN @Desc LIKE ''
                     THEN [Desc]
                     ELSE '%'+ @Desc +'%'
                END

This allows to return all values if the parameter is not defined (@Desc='') or return a subset of values (@Desc='test').

If you will only ever pass an empty string or a non-empty string (and not NULL) then doesn't this do the same thing: `WHERE [Desc] LIKE '%' + @Desc + '%';`? Even if you do pass NULL, `WHERE [Desc] LIKE '%' + COALESCE(@Desc, '') + '%';` — Aaron Bertrand, Jan 05 '18 at 15:23
Related, possibly [duplicate](https://stackoverflow.com/a/3415629). If this is a Stored Procedure, and `Better` means "more performant", you would be better off with dynamic SQL (or an ORM which customizes SQL) and drop the optional predicate altogether and instead run different queries for the branches. Reason? Query Plan. — StuartLC, Jan 05 '18 at 15:26
Aside: Adding wildcards (`%`) in the `where` clause precludes using `@Desc` to search for string starting or ending with a pattern, e.g. `'Mac%'`. It also precludes using an index lookup, although an index _scan_ is still possible. — HABO, Jan 05 '18 at 15:28
@HABO Generally speaking, a wildcard at the end of a string will still allow the index to be utilized. `'Mac%'` will work, but '%Mac' will not. Additionally, you can build an index with the `reverse` value, to allow you to query `'%Mac'`. See [this question](https://stackoverflow.com/questions/1537645/in-the-beginning-of-like-clause/1537706#1537706) for a little bit of discussion. — RToyo, Jan 05 '18 at 15:36

Jayasurya Satheesh · Accepted Answer · 2018-01-05T16:39:18.350

3

Use OR Operator instead of Case

DECLARE @Desc VARCHAR(200) = ''

SELECT [id],
       [Desc],
       [Col1],
       [Col2]
FROM [dbo].[tbl]
WHERE 
    (
        ISNULL(@Desc,'')=''
    )
    OR
    (
        ISNULL(@Desc,'')<>''
        AND
        [Desc] LIKE '%'+ @Desc +'%'
    )

Execution Plan Difference using Both Logics

Using Case

Using Or

edited Jan 05 '18 at 16:39

answered Jan 05 '18 at 15:27

Jayasurya Satheesh

7,826
3
22
39

1

Can you clarify if this is a _better_ way, or "just" an _alternative_ way? – RToyo Jan 05 '18 at 15:29
It's better way because using Case statements in Where clause is not a goo Programming strategy – Jayasurya Satheesh Jan 05 '18 at 15:31
3

Why is using a case expression a bad programming strategy? And why is using nonSARGable predicates better? – Sean Lange Jan 05 '18 at 15:42
@SeanLange To help my own understanding: does this answer introduce any _new_ non-sargable predicates that the OP's query didn't already have (`[Desc] LIKE '%'+@Desc+'%'`)? Does running a variable through `isnull` before comparison impact performance in any meaningful way in this scenario? – RToyo Jan 05 '18 at 16:16
@SeanLange, it's too broad to explain it here. Please refer this article http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx – Jayasurya Satheesh Jan 05 '18 at 16:20
1

@RToyo no there are no newly introduced nonSARGable predicates. Wrapping a variable in a function is not an issue. – Sean Lange Jan 05 '18 at 16:24
@JayasuryaSatheesh that article doesn't even pretend to back up the claims that a case expression is worse for performance. And their comments about performance are vague at best. Unless there are some actual facts to back up those claims it is just one persons speculation. – Sean Lange Jan 05 '18 at 16:28
1

@SeanLange I've added the difference in execution plan to the answer – Jayasurya Satheesh Jan 05 '18 at 16:39
Those two queries do not do the same thing though. And such a simple query with only a table scan is not really proof. This would need to scaled out with a million rows to really see the differences. What I am getting at is that unless you have some proof that using a case expression in the where clause is bad just stating that it is bad is not great either. – Sean Lange Jan 05 '18 at 16:46
The only reason I can think of that would make the `case` perform worst is the fact that all branches of a `case` statement will be evaluated regardless of the `case` condition, while the `or` condition inside `where` clause **might** be optimized out (though SQL Server does not guarantee short-circuit conditions, it **might** use them if the query optimizer see fit) – Zohar Peled Jan 08 '18 at 08:07
It is the most versatile option for my needs – user3247900 Jan 08 '18 at 10:34

score 2 · Answer 2 · answered Jan 05 '18 at 17:28

It's better for the execution engine to do as much parameter handling prior to the query.

DECLARE @Desc VARCHAR(200) = '';
DECLARE @SelectAll bit;

SET @SelectAll = CASE WHEN @Desc = '' THEN 1 ELSE 0 END;
SET @Desc = CASE WHEN @Desc = '' THEN @Desc ELSE ('%' + @Desc + '%') END;


SELECT [id],
       [Desc],
       [Col1],
       [Col2]
FROM [dbo].[tbl]
WHERE 
    (@SelectAll = 1)
        OR
    (@SelectAll = 0 AND [Desc] LIKE @Desc);

If you don't mind code duplication, you can take it even further and do two separate queries split by IF / ELSE.

score 1 · Answer 3 · answered Jan 05 '18 at 18:13

1

If you use null you save some steps

declare @userId  int = null; 

SELECT TOP 1000 [AuctionId]
      ,[UserId]
      ,[BiddingPrice]
      ,[DateTime]
  FROM [Test].[dbo].[Bid] 
  WHERE isnull(@userId, [UserId]) = [UserId];

answered Jan 05 '18 at 18:13

paparazzo

44,497
23
105
176

1

The OP is using `like` with wildcards. Something like this might be more appropriate to the question: `WHERE [UserId] LIKE '%' + isnull(@userId, '') + '%'` – RToyo Jan 05 '18 at 19:22

score 1 · Answer 4 · answered Jan 07 '18 at 07:41

Well, As Aaron Bertrand commented, your current query can be written simply like this:

DECLARE @Desc VARCHAR(200) = ''

SELECT [id],
       [Desc],
       [Col1],
       [Col2]
FROM [dbo].[tbl]
WHERE [Desc] LIKE '%'+ @Desc +'%'

Since if @Desc contains an empty string, it will result with [Desc] LIKE '%%' -so all records where [Desc] is not null will be returned anyway.

If @Desc can be passed as null, use Coalesce to convert null to an empty string:

...WHERE [Desc] LIKE '%'+ COALESCE(@Desc, '') +'%'

Please note that in both questions, records where the Desc column contains null will not be returned. If that is a nullable column and you want to also return the records where it's null and the @Desc parameter is also null or empty, then you should use OR:

SELECT [id],
       [Desc],
       [Col1],
       [Col2]
FROM [dbo].[tbl]
WHERE [Desc] LIKE '%'+ @Desc +'%' 
OR (COALESCE(@Desc, '') = '' AND [Desc] IS NULL)

Also, please note that this is only because of your use of LIKE - Should you try to evaluate conditions using a different operator (such as =, <, >etc') you should use the OR syntax like in the other answers.

Using of CASE in WHERE clause to evaluate empty parameters

4 Answers4