Re: SQL query

PRatcliff schrieb:
> Hello,
>
> 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 @userinput = 'A'
> THEN A.datefieldA BETWEEN @startdate AND @enddate
> ELSE B.datefieldB BETWEEN @startdate AND @enddate END
>

[...]
>
> 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

SELECT *
FROM tableA A
join tableB B
on A.idfield = B.idfield
WHERE (@userinput =A AND A.datefieldA BETWEEN @startdate AND @enddate)

   OR (@userinput!=A AND B.datefieldB BETWEEN @startdate AND @enddate)

HTH
Wilhelm Received on Wed Feb 18 2004 - 23:53:14 CST