SQL Server : IF condition within where clause

Question

I am trying to put an if condition within a where clause and I cannot figure out the correct syntax for what I want.

The logic should be if a certain period is equal to a specific number then check to make sure pdftype is not null else then don't check it all.

Here's what I have so far:

where  
    g.ReportInstanceID = blah
    and rcr.FormID = blah
    and rcr.FormSectionID = blah
    and rcr.SubSectionID = blah
    and CASE rcr.DataCollectionPeriodID 
           WHEN 163 THEN (PDFType IS NOT NULL)
        END

I would use multiple conditions with `OR` as shown in answer below, but you can use `CASE` in `WHERE`: `AND CASE WHEN rcr.DataCollectionPeriodID = 163 THEN PDFType ELSE 'X' END IS NOT NULL` — Hart CO, Jan 30 '17 at 18:31
[This](http://stackoverflow.com/a/10260297/92546) answer shows how a `CASE` expression can be used in a `ON` clause. The same works for a `WHERE` clause. — HABO, Jan 30 '17 at 19:35

score 6 · Accepted Answer · answered Jan 30 '17 at 18:24

That's not how the CASE statement works. I think this does what you want:

where  g.ReportInstanceID = blah
    and rcr.FormID = blah
    and rcr.FormSectionID = blah
    and rcr.SubSectionID = blah
    and (
          (rcr.DataCollectionPeriodID = 163 and PDFType IS NOT NULL)
          or rcr.DataCollectionPeriodID <> 163
        )

score 1 · Answer 2 · answered Jan 30 '17 at 18:23

1

Try this:

. . .
AND CASE when rcr.DataCollectionPeriodID = 163 THEN
     case when PDFType is not null then 1 else 0 end
    else 1 END = 1

answered Jan 30 '17 at 18:23

Gurwinder Singh

38,557
6
51
76

SQL Server : IF condition within where clause

2 Answers2