SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems