Bhavbhuti,
> I have a SELECT statement which needs to have a WHERE clause adapt if a
> variable has a value or not:
>
> WHERE CASE WHEN 72 <> 0 THEN tDevelopment.iID = 72
> ELSE (CAST(tDevelopment.tDT AS DATE) BETWEEN (SELECT US1.dValue
> FROM dSetValue('Current Year', 'From') US1)
> AND (SELECT US2.dValue
> FROM dSetValue('Current Year', 'To') US2)) END
> The value 72 is variable. What I need to do is when this value is a non zero
> I
> want to get only one record which matches the variable value and iID
>
> ELSE I want it to adapt to get a range of records as per stored dates
>
> Can we have such a WHERE clause? (The one above does not work for
> me) What if there are more such varied conditions to apply?
First, what you need to do is think of each test as returning a true/false
result, and then format the tests as follows:
WHERE 1 =
CASE
WHEN (Param <> 0) THEN 1
WHEN (Some OtherTest) THEN 1
WHEN (Yet another Test) THEN 1
ELSE 0
END
Sean
