SQL Conditional Where

Question

Hi there, I have a Stored Procedure called spGetOrders which accepts a few parameters: @startdate and @enddate. This queries an "Orders" table. One of the columns in the table is called "ClosedDate". This column will hold NULL if an order hasn't been closed or a date value if it has. I'd like to add a @Closed parameter which will take a bit value. In a simple world, I'd be able to do..

select * from orders o
where o.orderdate between @startdate AND @enddate
and (if @Closed = 1 then o.ClosedDate IS NULL else o.ClosedDate IS NOT NULL)

Obviously, that's not going to work.. I'm also looking at dynamic sql which is my last resort, but starting to look like the answer..

Please help..

Answer 1

SELECT *
FROM orders
WHERE orderdate BETWEEN @startdate AND @enddate
AND (@Closed = 1 OR CLosedDate IS NOT NULL)

Answer 2

Try this:

select * from orders o
where o.orderdate between @startdate AND @enddate
and ((@Closed = 1 And o.ClosedDate IS NULL) Or (@Closed = 0 And o.ClosedDate IS NOT NULL))

Be vary careful about mixing AND's and OR's in the where clause. When doing this, the parenthesis to control the order of evaluation is VERY important.

Answer 3

Or this:

select * from orders o
where o.orderdate between @startdate AND @enddate
and (  (@Closed = 1 AND o.ClosedDate IS NULL)
     OR (ISNULL(@Closed, 0) <> 1 AND o.ClosedDate IS NOT NULL)
     )

It looks like you want all the orders between two dates that have inconsistent Close information. The other suggestions are probably as good (or better) but I'm pretty sure that this works and is readable to me (most of the other suggestions appeared as I was typing).

Good luck!

Answer 4

Basicly, write it out.

select * from orders o
where o.orderdate between @startdate AND @enddate
and ((@Closed = 1 and o.ClosedDate IS NULL)
    or (@Closed != 1 and o.ClosedDate IS NOT NULL))

double, can be removed