Case Statement

The SQL Server case statement is a conditional statement that returns a single value based on the evaluation of a statement. Case statements can be used in a SELECT list, WHERE clause and even an ORDER BY clause. Case statement can also be nested. This provides a lot of pliability for evaluating multiple expressions.

We’ll start by walking through a simple case statement. There are two ways to formulate a case statement. One is to present the column or variable to evaluate after the word case. It should be noted that the else portion of a case statement is completely optional.

Basic Equality Evaluation

DECLARE @Season VARCHAR(100)
SET @Season = 'Summer'
DECLARE @Climate VARCHAR(50)

SET @Climate =
CASE @Season
    WHEN 'Winter' THEN 'Cold'
    WHEN 'Spring' THEN 'Mild'
    WHEN 'Summer' THEN 'Hot'
    WHEN 'Fall'   THEN 'Cool'
    ELSE 'Unknown'
END

SELECT @Climate

That syntax works well when evaluating whether single variable is equal to a value. If we want to add additional criteria then we have to formulate the statement a different way.

Basic Multi-Evaluation

DECLARE @Temperature INT
SET @Temperature = 64
DECLARE @Climate VARCHAR(50)

SET @Climate =
CASE
    WHEN @Temperature < 0 THEN 'Way Too Cold'
    WHEN @Temperature BETWEEN 0 AND 50 THEN 'Cold'
    WHEN @Temperature BETWEEN 51 AND 60 THEN 'Cool'
    WHEN @Temperature BETWEEN 61 AND 70 THEN 'Mild'
    WHEN @Temperature BETWEEN 71 AND 80 THEN 'Warm'
    WHEN @Temperature BETWEEN 81 AND 100 THEN 'Hot'
    WHEN @Temperature > 100 THEN 'Way Too Hot' 
    ELSE 'Unknown'
END

SELECT @Climate

Since our test does not consist of a simple “equals” we need to move each condition into the WHEN evaluator.

Evaluating from a Column

The examples above outlined the case statement returning a value to a variable, however we can also create a calculated column on the fly using CASE. To run this example, download the presidents table here.

    SELECT
        President
        ,WasElected =
        (CASE  
            WHEN ElectoralVotes IS NULL THEN 0
            ELSE 1
        END)
    FROM dbo.Presidents p

As you can see, when the ElectoralVotes column is null then we set our new virtual column equal to 0 or Not Elected.

Summarizing and Pivoting Data

The case statement is also useful in summarizing and pivoting data. The example below uses the case statement to count the president’s ages and SUM the age at inauguration into the appropriate category.

    SELECT
        AgeUnder45 =
        SUM((CASE WHEN AgeAtInauguration < 45 THEN 1 END))
        ,AgeBetween45And55 =
        SUM((CASE WHEN AgeAtInauguration BETWEEN 45 AND 55 THEN 1 END))
        ,AgeBetween56And65 =
        SUM((CASE WHEN AgeAtInauguration BETWEEN 56 AND 65 THEN 1 END))
        ,AgeBetween56And65 =
        SUM((CASE WHEN AgeAtInauguration BETWEEN 56 AND 65 THEN 1 END))
        ,AgeOver65 =
        SUM((CASE WHEN AgeAtInauguration > 65 THEN 1 END))     
    FROM dbo.Presidents p

10 comments
sema 16 Aug 2011 at 11:51 am

is there any limitation of the number of case statements that we can use in one select statement.
thanks…

Derek Dieter 16 Aug 2011 at 11:52 am

Hi Sema, no there is not.

Good luck,
Derek

Jasvinder 14 Jan 2011 at 4:17 am

Name =
Case @searchString
when ‘ ‘ then Not SurvUserHier.EmpId
When ‘eq’ then SurvUserHier.EmpId
end)

I want to use NOT in the then, but its giving error any suggestions

Derek Dieter 16 Aug 2011 at 11:54 am

Hi Jasvinder,

In that case (hehe).. you need to break the case statement out.

Name =
Case when @searchString = ‘eq’ THEN SurvUserHier.EmpId END

You don’t need an ELSE.

Hans Fransson 08 Nov 2010 at 2:30 pm

Hi!
I wonder if you can help me with a convert problem in a case expression?
The result would be like..
name, customer
Anna, 5
Hans, 10
Curt, No customer
I must make an convert in the case, and here is my code…
WHEN COUNT( k.customer) = 0 THEN ‘No customer’
ELSE COUNT(CONVERT(char(20),CONVERT(int, k.customer)))
What is wrong!
Please help!
Hans Fransson // Sweden

Featured Articles

 Site Author