Introduction to the CASE Expression By Andy Warren

People often get confused whether to use if else statements or case statements in T-SQL. According to Andy, only case statements can be used in T-SQL statements. If else statements cannot be used in select statements because if else statements can be used in control flow. Following are some of the situations and syntax that can be used with case statements. The most important is included in the last syntax (Syntax 5)

Syntax 1:case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'end as aliascolumnnameThe drawback with the above syntax is when the conditions of the when statement is not matched the result returned is null in the column.

Syntax 2:case columnname when 'exisitng value1' then 'new value1'when 'exisitng value2' then 'new value2'else columnameend as aliascolumnnameWhen the else condition is used the drawback in Syntax1 is fixed.

Syntax 3:If you want to use case statement for multiple columns the syntax is as follows;case when column1='exisitng value' then 'new value'when column2='exisitng value' then 'new value'else column1end as aliascolumnnameNotice that there is no column name after the case in the above statement.

Syntax 4:Nested case statementswhen formatted looks better and you can understand bettercase when columnname1 = 'exisitng value' then case when clumnname2 = 'exisitng value1' then 'new value' else columnname1 end else columnname1end as aliascolumnnameThe above can also be written in one case statement doing two tests.

Syntax 5 Last but not the least (Very important learning of the day):You can use case in the order by clauseselect columnname from table name order by case when columnname = 'value1' then 0 else 1 end, columnname.