The Power of the SQL Server Case Statement

The SQL Server Case Statement is a powerful tool for writing conditional logic in SQL. It allows you to write an if-else statement in SQL without having to use multiple queries or complex programming. You can create a single query that will evaluate conditions and return results based on those conditions. In this article, Let's see how it works and how it can be used to simplify your code.

Syntax

Let's start with the syntax. It is pretty simple as most SQL statements are.

--version 1

CASE input_expression
    WHEN compare_to1 THEN result1
    WHEN compare_to2 THEN result2
    WHEN compare_toN THEN resultN
    ELSE default_result
END


--version 2

CASE 
    WHEN boolean_expression1 THEN result1
    WHEN boolean_expression2 THEN result2
    WHEN boolean_expressionN THEN resultN
    ELSE default_result
END

The difference is a little subtle, in version 1 you indicate what value or input you want to examine then in each WHEN statement you list the values you want to compare to, the THEN statement outputs the result if that is a match. In version 2, you go straight to the WHEN clauses and indicate a boolean expression to check for. Both versions have a default value if all the WHENs fail.

Examples

Let's look at few examples to see this in action. Let's create a grade variable and let's test on its value and show the appropriate note based on the grade.

-- with version 1 

DECLARE @grade varchar(1)
SET @grade = 'B'

SELECT CASE @grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Ok'
    ELSE 'Needs a lot of work'
END

-- with version 2

DECLARE @grade varchar(1)
SET @grade = 'B'

SELECT CASE
    WHEN @grade = 'A' THEN 'Excellent'
    WHEN @grade = 'B' THEN 'Good'
    WHEN @grade = 'C' THEN 'Ok'
    ELSE 'Needs a lot of work'
END

Both examples output "Good" to the screen.

Within SELECT Statements

The Case statement really shines when used within a SELECT statement, it allows us to create let's say a column (or columns) on the fly and apply conditional logic and have it part of the result set.

To Illustrate that, let's create some sample data. Let's create a quick table, insert some records in it, and build a result set that uses the same logic we saw earlier in our examples.

DECLARE @SampleTable TABLE (Name varchar(20), Grade varchar(1))

insert into @SampleTable values ('John', 'B')
insert into @SampleTable values ('Alice', 'A')
insert into @SampleTable values ('Erica', 'C')
insert into @SampleTable values ('Steve', 'D')

Now let's use our shinny new Case statement logic and add a Notes column that will output our logic to the user

SELECT Name,
	   Grade,
	   Notes = CASE
					WHEN Grade = 'A' THEN 'Excellent'
					WHEN Grade = 'B' THEN 'Good'
					WHEN Grade = 'C' THEN 'Ok'
					ELSE 'Needs a lot of work'
			   END 
FROM @SampleTable

Once we run this, the result is

Name                 Grade Notes
-------------------- ----- -------------------
John                 B     Good
Alice                A     Excellent
Erica                C     Ok
Steve                D     Needs a lot of work

And there you have it, a simple, yet powerful component of SQL syntax that allows for conditional logic with Case statements.