CODEWITHSUNDEEP
sqlcasewhere-clausecase-statement
TJH
TJH

Reputation: 199

SQL - Creating A Conditional Where Clause For This Simple Query

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

Upvotes: 3

Views: 4209

Answers (4)

user1082916
user1082916

Reputation: 

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

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

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.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 5

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

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

Upvotes: 2

Related Questions