Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Conditional Where

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

like image 823
madcolor Avatar asked Dec 09 '08 16:12

madcolor


People also ask

Can we put case in WHERE condition in SQL?

You can use a CASE Statement anywhere a valid expression is used within the SELECT statement such as the WHERE clause's filter criteria.

Can we use two conditions in WHERE clause in SQL?

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.

How do I do a conditional statement in SQL?

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.

Can we use function in WHERE clause?

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.


2 Answers

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.

like image 83
George Mastros Avatar answered Sep 18 '22 15:09

George Mastros


SQL Statement:

SELECT *  
FROM orders  
WHERE orderdate BETWEEN @startdate AND @enddate  
AND (@Closed = 1 OR CLosedDate IS NOT NULL)
like image 29
dkretz Avatar answered Sep 19 '22 15:09

dkretz