.NET Answers

I’ve had to do this a couple of times and I just realized I haven’t written about it anywhere.

If you need to SELECT a field from a row that returns different content based on the content of the field, you’d think, based on previous programming experience, that your code would look something like this.

SELECT IIF(field=2,"content true","content false")
   FROM DB

But you’d be wrong.

No, the people who created SQL thought a syntax with a little richer flexibility would do the trick.  Why limit ourselves to two states when multiple states will do?

SELECT CASE field WHEN 2 THEN "content true"     ELSE "content false" END
    FROM DB

In fact you can have multiple WHEN conditions in your code

SELECT CASE field
    WHEN 1 THEN 'One'
    WHEN 2 THEN 'Two'
    WHEN 3 THEN 'Three'
    END FROM DB

You can also use multiple fields

SELECT CASE
   WHEN field1 = 1 THEN 'field one'
   WHEN field2 = 2 THEN 'field two'
   ELSE 'Something else'
   END FROM DB

Related Post

• SQL – Filtering WHERE condition on two rows
• Random in SQL – SQL For Programmers
• SQL CURSOR Performance – SQL For Programmers
• SQL CURSOR – SQL For Programmers
• Temporary Tables – SQL For Programmers

2 Responses to “SQL SELECT CASE Instead of IIF”