A Simple example of a Conditional Where Clause in SQL Server

I was recently preparing a query for a report where the client needed to find out the following information from a small table -

- Products that have been Discontinued but are receiving Orders

- Products that have not been Discontinued but do not generate Orders

I solved this requirement using Conditional WHERE Clause. The Products table of the Northwind database has fields that match this requirement and I will demonstrate the Conditional WHERE clause query using the same. Here’s the query:

SELECT ProductID, ProductName, UnitsOnOrder, Discontinued from
Products WHERE
(Discontinued = 1 AND UnitsOnOrder > 0)
 OR
(Discontinued  = 0 AND UnitsOnOrder = 0)

OUTPUT

You can also branch the statements and re-write the query using an IF-ELSE statement, however since this was a small table, I went in for the Conditional WHERE clause.