Menu
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..
823
You can use a CASE Statement anywhere a valid expression is used within the SELECT statement such as the WHERE clause's filter criteria.
You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.
Any T-SQL statement can be executed conditionally using IF… ELSE. If the condition evaluates to True, then T-SQL statements followed by IF condition in SQL server will be executed. If the condition evaluates to False, then T-SQL statements followed by ELSE keyword will be executed.
You can't use window functions in WHERE , because the logical order of operations in an SQL query is completely different from the SQL syntax. The logical order of operations in SQL is: FROM, JOIN.
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.
83
SQL Statement:
SELECT *
FROM orders
WHERE orderdate BETWEEN @startdate AND @enddate
AND (@Closed = 1 OR CLosedDate IS NOT NULL)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
