SQL - Creating A Conditional Where Clause For This Simple Query

Question

I've been trying to create a conditional where clause for my query below but I keep seeing so many alternatives I'm not sure what to use in this case.

What I need is something along the lines of this: (though of course this code is wrong)

where casCaseType='m'
  and casCurrentWorkflowID=990
  and cmsDateCreated between @FromDate and @ToDate

 CASE @WFStatus
      WHEN @WFStatus=1 then eveworkflowID<100
      WHEN @WFStatus=2 then eveworkflowID<200
      WHEN @WFStatus=3 then eveworkflowID<300
      WHEN @WFStatus=4 then eveworkflowID<400
 ELSE 0
 END

So when I choose the WFStatus parameter as 1, it would automatically engage that section of the where clause bringing out only those results with a eveworkflowID which is less than 100.

Any help would be greatly appreciated!

Thanks

I assume "and eveworkFlowID < @WFStatus*100" wouldn't do it? — Glenn, Mar 08 '12 at 12:09

score 5 · Accepted Answer · answered Mar 08 '12 at 12:09

WHERE casCaseType='m'
  AND casCurrentWorkflowID=990
  AND cmsDateCreated between @FromDate and @ToDate

  AND eveworkflowID < 
      CASE @WFStatus
        WHEN 1 THEN 100
        WHEN 2 THEN 200
        WHEN 3 THEN 300
        WHEN 4 THEN 400
        ELSE 0
      END

score 2 · Answer 2 · answered Mar 08 '12 at 12:08

2

I am not sure, but if I understand correctly: ... AND eveworkflowID < @WFStatus * 100

answered Mar 08 '12 at 12:08

Guillaume Poussel

9,572
2
33
42

score 1 · Answer 3 · answered Mar 08 '12 at 12:09

where casCaseType='m'
    and casCurrentWorkflowID=990
    and cmsDateCreated between @FromDate and @ToDate
    and (@WFStatus BETWEEN 1 AND 4 AND eveworkflow < @WFStatus * 100)

EDIT
Didn't mind the case that @WFStatus is not between 1 and 4, which is covered by your default case.

score 0 · Answer 4 · answered Mar 08 '12 at 12:29

where casCaseType='m'
and casCurrentWorkflowID=990
and cmsDateCreated between @FromDate and @ToDate

  CASE eveworkflowID<
  WHEN @WFStatus=1 then 100
  WHEN @WFStatus=2 then 200
  WHEN @WFStatus=3 then 300
  WHEN @WFStatus=4 then 400
 ELSE 0
END

SQL - Creating A Conditional Where Clause For This Simple Query

4 Answers4