Using SQL Server CASE statement in WHERE

Question

I want to select records from a table in a stored procedure. Given parameters can be empty or a string including some keys separated by comma (1, 2, etc) I want to manage that when a parameter is an empty string, "WHERE" ignore searching. I'm using this code:

where (CASE when @PatientID <> 0 then ( dental.ID_Sick in (1,2)) else (1=1) end)

Something like that is working in W3School. I mean:

SELECT * FROM Customers
WHERE (case when 1=1 then (Country IN ('Germany', 'France', 'UK')) else 1=1 end);

What is the problem in my query that does not work? SQLServerManagementStudio is giving error on "IN" statement.

It's generally better to use AND/OR constructions instead of case expressions in the WHERE clause. — jarlh, Feb 13 '18 at 08:35
refer to this, similar problem is explained in here. https://stackoverflow.com/questions/11232267/using-case-statement-inside-in-clause — Ven, Feb 13 '18 at 08:38
@Shaho don't. "Dynamic" filters result in bad execution plans. If you *don't* want to use the @PatientID parameter, *don't* use the @PatientID parameter. *Don't* create such a `catch-all` stored procedure. Use an ORM like EF and LINQ if you need to use different parameters — Panagiotis Kanavos, Feb 13 '18 at 08:38
The problem with your current syntax is you have no boolean result. A CASE **expression** is only one side of the coin. it's not a `CASE` statement, which is where you've understanding is wrong. `CASE i WHEN 1 then 'Left' ELSE 'Right' END = 'Right'` would be a Boolean result for a `WHERE`. You don't put the boolean logic in side the `WHEN`/`ELSE`. — Thom A, Feb 13 '18 at 08:38
@Shaho the server caches the execution plan of the *first* invocation. This means that if `@PatientID` is `1` the server may decide to use an index seek on `ID_Sick` even for queries that don't need it. Or for `0`, it may decide to use a table scan when an index seek on `ID_Sick` would be better — Panagiotis Kanavos, Feb 13 '18 at 08:42
@Shaho can you provide some example closer to your actual parameters? — M.Ali, Feb 13 '18 at 08:52
@Shaho what is the *client's* language? How do you intend to call that query? There are ways to create ad-hoc safely in most languages and reporting tools — Panagiotis Kanavos, Feb 13 '18 at 08:55
@M.Ali for example think that I'm making some filters on select statement. the way is using WHERE. but my filters are not unique. in a field like Patient can be a lot of choices. but 0 means nothing — Shaho, Feb 13 '18 at 08:58
@PanagiotisKanavos because of I'm making a stored procedure it is not possible to create query in client ... — Shaho, Feb 13 '18 at 08:59
@Shaho don't create a stored procedure and create the query in the client then. Or explain the *actual* problem so people can explain how to avoid using such CASE or `Or 1=1` statements — Panagiotis Kanavos, Feb 13 '18 at 09:01
@Shaho for example you could use a UNION ALL of two different queries. Or use a table variable instead of `IN`. Or pass ID values as a TVP — Panagiotis Kanavos, Feb 13 '18 at 09:03
@PanagiotisKanavos I have a very huge query that it is too hard to manage in client — Shaho, Feb 13 '18 at 09:03
@Shaho and you are making it huger by adding all those `OR` and `CASE` statements. Besides, why can't you create a *view* to simplify that query? Where is that query anyway? Using a technique that is well-known to be bad won't make the complex query easier — Panagiotis Kanavos, Feb 13 '18 at 09:04
@Shaho besides, more complex querie are *more* susceptible to bad performance when used with "dynamic parameters" simply because there are *more* operations that can go wrong. More fields that could be covered by indexes and end up as table scans or vice versa — Panagiotis Kanavos, Feb 13 '18 at 09:05

score 2 · Answer 1 · answered Feb 13 '18 at 09:29

Solution:

The best way to handle such optional parameters is to use dynamic SQL and built the query on the fly. Something like....

CREATE PROCEDURE myProc
 @Param1    VARCHAR(100)    = NULL
,@Param2    VARCHAR(100)    = NULL
,@Param3    VARCHAR(100)    = NULL
,@ListParam VARCHAR(100)    = NULL
--, etc etc...
AS
BEGIN
    SET NOCOUNT ON;
    Declare @Sql NVARCHAR(MAX);

SET @Sql = N'   SELECT * 
                FROM TableName
                WHERE 1 = 1 '

-- add in where clause only if a value was passed to parameter
        +  CASE WHEN @Param1 IS NOT NULL THEN 
           N' AND SomeColumn = @Param1 ' ELSE N'' END

-- add in where clause a different variable 
-- only if a value was passed to different parameter

        +  CASE WHEN @Param2 IS NOT NULL THEN 
           N' AND SomeOtherColumn = @Param3 ' ELSE N'' END

-- List Parameter used with IN clause if a value is passed

        +  CASE WHEN @ListParam IS NOT NULL THEN 
           N' AND SomeOtherColumn IN (
                                     SELECT  Split.a.value(''.'', ''VARCHAR(100)'') IDs
                                      FROM (
                                             SELECT Cast (''<X>'' 
                                                          + Replace(@ListParam, '','', ''</X><X>'') 
                                                          + ''</X>'' AS XML) AS Data
                                              ) AS t CROSS APPLY Data.nodes (''/X'') AS Split(a)  ' 
            ELSE N'' END

Exec sp_executesql    @sql 
                    , N' @Param1 VARCHAR(100), @Param2 VARCHAR(100) ,@Param3 VARCHAR(100) ,@ListParam VARCHAR(100)'
                    , @Param1 
                    , @Param2 
                     ,@Param3 
                    , @ListParam 


END

Problem with Other approach

There is a major issue with this other approach, you write your where clause something like...

WHERE ( ColumnName = @Parameter  OR @Parameter IS NULL)

The Two major issues with this approach

1) you cannot force SQL Server to check evaluate an expression first like if @Parameter IS NULL, Sql Server might decide to evaluate first the expression ColumnName = @Parameterso you will have where clause being evaluated even if the variable value is null.

2) SQL Server does not do Short-Circuiting (Like C#), even if it decides to check the @Parameter IS NULL expression first and even if it evaluates to true, SQL Server still may go ahead and evaluating other expression in OR clause.

Therefore stick to Dynamic Sql for queries like this. and happy days.

Menno · Answer 2 · 2018-02-13T08:40:29.943

1

It would be better readable to rewrite your statement as follows:

WHERE @PatientID = 0 
OR dental.ID_Sick in (1,2)

Referring to your actual question, I'd advise to read the linked question as provided by B House.

edited Feb 13 '18 at 08:40

answered Feb 13 '18 at 08:36

Menno

12,175
14
56
88

2

Better yet just don't use the `@PatientID` parameter at all. Such `catch all` queries result in bad execution plans – Panagiotis Kanavos Feb 13 '18 at 08:40
@PanagiotisKanavos True, but that kind of depends on the tools as used by Shaho. E.g. SSRS or C#. He might as well be learning about the concepts of t-sql and not be building an actual production tool. – Menno Feb 13 '18 at 08:44
But the logic of your answer is not my goal ... I have some other filters to do in that select statement. – Shaho Feb 13 '18 at 08:45
@Shaho The use of a CASE-statement does not give you more possibilities than (nested) AND/OR constructions. Both are based on the logic IF this THEN that, where this and that are the boolean result of some statement. – Menno Feb 13 '18 at 08:48
@Shaho It answers the stated question. – paparazzo Feb 13 '18 at 08:50
@Shaho the attempt to create dynamic filters is the actual problem. As for the syntax error, `CASE` is *not* a logical operation. You simply can't use it this way. You'll have to use `OR`. Or, perhaps, you tried to copy code that compares the *field* to the parameter value or the field itself? – Panagiotis Kanavos Feb 13 '18 at 08:54

score 1 · Accepted Answer · answered Feb 13 '18 at 08:51

1

SQL Server does not have a Bool datatype, so you can't assign or return the result of a comparison as a Bool as you would in other languages. A comparison can only be used with IF-statements or WHERE-clauses, or in the WHEN-part of a CASE...WHEN but not anywhere else.

Your specific example would become this:

SELECT * FROM Customers
WHERE 1=1 OR Country IN ('Germany', 'France', 'UK')

answered Feb 13 '18 at 08:51

Peter B

22,460
5
32
69

Almost no database has a `bool` data type. I suspect the single exception, Postgresql, doesn't allow a return value to be used in logical operations either. What would you do with nulls? Compared against `true`, `false` or `NULL` maybe – Panagiotis Kanavos Feb 13 '18 at 08:53

score 0 · Answer 4 · answered Feb 13 '18 at 09:03

0

May be this straight way will work for you

IF (@PatientID <> 0)
    BEGIN
       SELECT * FROM Customers
       WHERE Country IN ('Germany', 'France', 'UK')
    END

answered Feb 13 '18 at 09:03

Newaz Sharif

424
4
12

score -3 · Answer 5 · answered Feb 13 '18 at 08:45

-3

try this:

    WHERE 1=(CASE WHEN @PatientID <>0 AND dental.ID_Sick in (1,2) THEN 1
            WHEN @PatientID =0 THEN 1
            ELSE 0
            END)

answered Feb 13 '18 at 08:45

Sahi

1,454
1
13
32

Using SQL Server CASE statement in WHERE

5 Answers5

Problem with Other approach