Execute the following SQL Server T-SQL script in SSMS Query Editor to demonstrate the usage of CASE function in a WHERE clause:

USE AdventureWorks

GO

-- SQL case in where - SQL case function in where clause - SQL inner join

SELECT   Category = c.Name,

         Subcategory = sc.Name,

         ProductName = p.Name,

         ProductNumber,

         Color = isnull(Color,''),

         COST = '$' + convert(VARCHAR,StandardCost,1),

         ListPrice = '$' + convert(VARCHAR,ListPrice,1),

         ProductLine

FROM     Production.Product p

         INNER JOIN Production.ProductSubcategory sc

           ON p.ProductSubcategoryID = sc.ProductSubcategoryID

         INNER JOIN Production.ProductCategory c

           ON sc.ProductCategoryID = c.ProductCategoryID

WHERE    p.Name LIKE (CASE ProductLine

                        WHEN 'R' THEN 'Road%'

                        WHEN 'M' THEN 'Mountain%'

                        WHEN 'T' THEN 'Touring%'

                        WHEN 'S' THEN '%Shorts%'

                        ELSE 'Not for sale%'

                      END)

ORDER BY Category,

         Subcategory,

         ProductName;

GO

/* Partial results

Category    Subcategory             ProductName             ProductNumber

Accessories Bottles and Cages       Mountain Bottle Cage    BC-M005

Accessories Bottles and Cages       Road Bottle Cage        BC-R205

Accessories Panniers                Touring-Panniers, Large PA-T100

Accessories Pumps                   Mountain Pump           PU-M044

Accessories Tires and Tubes         Mountain Tire Tube      TT-M928

Accessories Tires and Tubes         Road Tire Tube          TT-R982

Accessories Tires and Tubes         Touring Tire            TI-T723

Accessories Tires and Tubes         Touring Tire Tube       TT-T092

*/

Related article:

http://www.sqlusa.com/bestpractices/training/scripts/casefunction/