If we want to use If..Else, WHEN or similar types of condition in our SQL query, its need to be done by using CASE…..END. More details can be found in:
Please look at the examples at the end of msdn link.
For me, I had to use it lots of places when I have started working in student ILR data XML data return. A part of the huge query using CASE: