Introduction

The CASE keyword is used as a conditional operator that considers a value, examines it, and acts on an option depending on the value. The formula of the CASE statement is:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result

	WHEN Value_n THEN Result
END

In the following example, a letter that represents a student is provided. If the letter is m or M, a string is created as Male. If the value is provided as f or F, a string is created as Female:

DECLARE @CharGender Char(1),
	@Gender  Varchar(20);
SET @CharGender = 'F';
SET @Gender = 
	CASE @CharGender
		WHEN N'm' THEN N'Male'
		WHEN N'M' THEN N'Male'
		WHEN N'f' THEN N'Female'
		WHEN N'F' THEN N'Female'
	END;

SELECT N'Student Gender: ' + @Gender;
GO

Here is the result of executing it:

In most cases, you may know the only types of value that would be submitted to a CASE statement. In some other cases, an unpredictable value may be submitted. If you anticipate a value other than those you are aware of, the CASE statement provides a "fit-all' alternative by using the last statement as ELSE. In this case, the formula of the CASE statement would be:

CASE Expression
	WHEN Value1 THEN Result
	WHEN Value2 THEN Result
	WHEN Value_n THEN Result
	
	ELSE Alternative
END

The ELSE statement, as the last, is used when none of the values of the WHEN statements fits. Here is an example:

DECLARE @CharGender Char(1),
	@Gender  Varchar(20);
SET @CharGender = 'g';
SET @Gender = 
	CASE @CharGender
		WHEN N'm' THEN N'Male'
		WHEN N'M' THEN N'Male'
		WHEN N'f' THEN N'Female'
		WHEN N'F' THEN N'Female'
		ELSE 'Unknown'
	END;

SELECT N'Student Gender: ' + @Gender;
GO

This would produce:

If you don't produce an ELSE statement but a value not addressed by any of the WHEN statements is produced, the result would be NULL. Here is an example:

This means that it is a valuable safeguard to always include an ELSE sub-statement in a CASE statement.