CASE Samples

Simple CASE Syntax: CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Example:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO


Using Select with Searched Case:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Name, 'Price Range' =
      CASE
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO


As a Replacement for IIF:

SELECT FirstName, LastName, TelephoneNumber,
     IIf(IsNull(TelephoneInstructions),"Any time",
     TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo;

Using Case in Order By:

ECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;


Using Case in Update statement:

USE AdventureWorks2008R2;
GO
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

Using Case in SET Statement:

SET @ContactType =
        CASE
            -- Check for employee
            WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e
                WHERE e.BusinessEntityID = @BusinessEntityID)
                THEN 'Employee'


            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.BusinessEntityContact AS bec
                WHERE bec.BusinessEntityID = @BusinessEntityID)
                THEN 'Vendor'


            -- Check for store
            WHEN EXISTS(SELECT * FROM Purchasing.Vendor AS v         
                WHERE v.BusinessEntityID = @BusinessEntityID)
                THEN 'Store Contact'


            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Sales.Customer AS c
                WHERE c.PersonID = @BusinessEntityID)
                THEN 'Consumer'
        END;


Using Case in Having Clause:

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
        THEN ph1.Rate
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F'
        THEN ph1.Rate 
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;