SQL query

I am constructing a stored procedure to run based on user input. What I WANT to do is basically this:

SELECT *
FROM tableA A
join tableB B
on A.idfield = B.idfield
WHERE CASE WHEN _at_userinput = 'A'

       THEN A.datefieldA BETWEEN _at_startdate AND @enddate
       ELSE B.datefieldB BETWEEN _at_startdate AND @enddate END

or this:

SELECT *
FROM tableA A
join tableB B
on A.idfield = B.idfield
WHERE if _at_userinput = A then A.datefieldA BETWEEN @startdate AND _at_enddate
else B.datefieldB BETWEEN _at_startdate AND @enddate

I know I can't use CASE in the above fashion, but I want to use a single query to pull data where the 'where' field can change depending upon the user's selection. How can I change the contstraint field in the WHERE clause based on a variable value?

Thanks in advance & cheers,
PRatcliff Received on Thu Feb 19 2004 - 00:56:24 CET