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