The SQL Server Case statement is best explained by example. What Case allows us to do is get a variable, or a field from a column in a table or a view and compare it with something else and give us a result based on the comparison. Case starts with the word Case and ends with the word End.

Case When

This first example returns “First option was chosen” under the column name “MyColumnName”.

declare @myOption as varchar(10) = 'Option A'

select case when @myOption = 'Option A' then 'First option'
            when @myOption = 'Option B' then 'Second option'
	    else 'No option' 
	    END + ' was chosen.' as MyColumnName
go

If you don’t have an else clause and case cannot find a match, then NULL is returned as in the code example below.

declare @myOption as varchar(10) = 'Option Z'
select case when @myOption = 'Option A' then 'First option'
            when @myOption = 'Option B' then 'Second option'
			--else 'No option' 
			END + ' was chosen.' as MyColumnName

More practically we will be using a table so we have another example.

select Department, DepartmentHead,
   -- case creates a whole new column called "MyColumn"
   case when left([DepartmentHead],1) = 'A' then 'Starts with A' 
        when left([DepartmentHead],1) = 'B' then 'Starts with B'
	when left([DepartmentHead],1) = 'C' then 'Starts with C'
	else 'Not A B or C' END + '.' as MyColumn
   from [dbo].[tblDepartment]

The results in SSMS are shown below.

Case @MyVariable When

There is another way to use case. This way is shorter to write/type but less versatile and really is the equivilent to the corresponding code above.

declare @myOption as varchar(10) = 'Option A'
select case @myOption when 'Option A' then 'First option'
                      when 'Option B' then 'Second option'
		      else 'No option' 
		      END + ' was chosen.' as MyColumnName