DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the CASE function in a WHERE clause?

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/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE