SQL CASE Statement in WHERE Clause
CASE Statement In SQL Select WHERE Clause
In the following t-sql sample SELECT code, you can see the SQL Server CASE statement in WHERE clause.
The sql CASE statement is applied over PerAssemblyQty column value of BillOfMaterials table in AdventureWorks2008R2 sample database.
If you check the CASE expression, you will see that according to the unit of measure used in a bill of material of a product, the filtering argument quantity has different values.
select
bom.*,
u.Name
from Production.BillOfMaterials bom
inner join Production.UnitMeasure u on u.UnitMeasureCode = bom.UnitMeasureCode
where
PerAssemblyQty >=
CASE u.Name
WHEN 'Boxes' THEN 1
WHEN 'Each' THEN 30
WHEN 'Ounces' THEN 9
WHEN 'Inch' THEN 40
ELSE 0
END
Using sql AdventureWorks sample database on Microsoft SQL Server 2008 R2 database instance, you can run the t-sql select statement examples.
SQL CASE Statement In WHERE Clause with BETWEEN
The following t-sql script has sql CASE in WHERE clause.
The WHERE condition filter applied for this sql sample is on OrderDate column.
If the SalesPersonId is null which means not known only a single date orders is requested.
But if SalesPersonId is not null which means the sales person is identified within the order header, then all orders between specific dates is selected.
select
SalesOrderID,
SalesPersonID,
OrderDate,
TotalDue
from Sales.SalesOrderHeader
where
OrderDate between (case when SalesPersonID is null then
'2006-05-31' else '2006-03-01' end)
and '2006-05-31'
Note that, the first part of sql BETWEEN clause is defined using a sql CASE conditional statement.