Home Using SQL Server CASE statement in WHERE
Reply: 5

Using SQL Server CASE statement in WHERE

Shaho Published in 2018-02-13 08:33:04Z

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.

Peter B
Peter B Reply to 2018-02-13 08:51:50Z

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')
Aquillo Reply to 2018-02-13 08:40:29Z

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.

Sahi Reply to 2018-02-13 08:45:41Z

try this:

    WHERE 1=(CASE WHEN @PatientID <>0 AND dental.ID_Sick in (1,2) THEN 1
            WHEN @PatientID =0 THEN 1
            ELSE 0
Newaz Sharif
Newaz Sharif Reply to 2018-02-13 09:03:50Z

May be this straight way will work for you

IF (@PatientID <> 0)
       SELECT * FROM Customers
       WHERE Country IN ('Germany', 'France', 'UK')
M.Ali Reply to 2018-02-13 09:29:45Z


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

 @Param1    VARCHAR(100)    = NULL
,@Param2    VARCHAR(100)    = NULL
,@Param3    VARCHAR(100)    = NULL
,@ListParam VARCHAR(100)    = NULL
--, etc etc...
    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 
                    , @ListParam 


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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.36038 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO