SQL - Create a conditional Where clause for this simple query
I'm trying to create a conditional where clause for my query below, but I see so many alternatives all the time, I'm not sure what to use in this case.
I need something like this: (although 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 select the WFStatus parameter as 1, it automatically uses that section of the where clause, outputting only those results with eveworkflowID less than 100.
Any help would be greatly appreciated!
thank
+3
TJH
source
to share
4 answers
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
+5
ypercubeᵀᴹ
source
to share
I'm not sure, but if I understand correctly:
...
AND eveworkflowID < @WFStatus * 100
+2
Guillaume poussel
source
to share
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 mean the case @WFStatus
is not between 1 and 4, which is covered by the default case.
+1
Thorsten dittmar
source
to share
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
0
user1082916
source
to share