Diffrent WHERE critera depending on inparameter

[marcarls]

I have this problem that user are going to provide an inparameter that I will use as a critera. User can choose from two diffrent dates or leave them out. This leave me tree diffrent scenario to handle in the query.
1. If date 1 is filled in, query shall return all rows where field XXX IS NOT NULL
2. If date 2 is filled in, query shall return all rows where field XXX IS NULL
3. If no date is selected, my query shall return all rows.

Is there anyway to write this in one select statement. I am going to use the query to build an report later on.

/Maria

 

[rac2]

Here is the logic of it, you may need to revise this depending on how you represent the dates when no value is selected and whether you are building the query in a script or in a stored procedure. I assume that no date is an empty string and this is in a stored procedure. The @date1 and @date2 are the "inparameter"'s.

SELECT * FROM aTable
WHERE
( @date1 <> '' AND fieldXXX IS NOT NULL )
OR
( @date2 <> '' AND fieldXXX IS NULL )
OR
( @date1 = '' AND @date2 = '' )