How to apply case in where clause in sql server?

Question

I have a query in SQL Server with the following where condition

where  (Convert(date,AppDate) between Convert(date,''+@DateFrom +'') AND
    Convert(date,''+@DateTo+'') and DrCode =  '' +@DrCode+'' )

But I want to add and DrCode = '' +@DrCode+'' when DrCode>0

How to set it?

Possible duplicate of [How do write IF ELSE statement in a MySQL query](https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query) — Sainath Krishnan, Sep 11 '18 at 08:31

score 2 · Answer 1 · answered Sep 11 '18 at 08:32

2

I don't know why you are doing string concatenations like this:

'' + @DateFrom + ''

In any case, I don't think a CASE expression is appropriate for what you want to do. Just spell out the logic directly in the WHERE clause:

WHERE
    CONVERT(date, AppDate) BETWEEN
        CONVERT(date, @DateFrom) AND CONVERT(date, @DateTo) AND
        ((DrCode = @DrCode AND DrCode > 0) OR DrCode <= 0);

answered Sep 11 '18 at 08:32

Tim Biegeleisen

502,043
27
286
360

The double single quotes make me think the code in the question is a snippet from a dynamic SQL string. Just a hunch, though. – Eric Brandt Sep 11 '18 at 14:37
@EricBrandt That might explain it. Anyway, I don't think that would change the logic I am presenting one way or the other. – Tim Biegeleisen Sep 11 '18 at 15:18
I agree. The logic holds up. – Eric Brandt Sep 11 '18 at 15:45

score 0 · Answer 2 · answered Sep 11 '18 at 08:46

If you want to use DrCode > 0 condition with DrCode = '' +@DrCode+'' separately then you can try the following:

where (Convert(date,AppDate) between Convert(date,''+@DateFrom +'') AND
    Convert(date,''+@DateTo+'')) and 
    (CASE WHEN DrCode > 0 AND DrCode =  '' +@DrCode+'' THEN 1 ELSE 0 END =1)

How to apply case in where clause in sql server?

2 Answers2