.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

Other Related Items:

I Love Else Wooden KeychainThe wood is 2" x 1" and the chain is about 1" long - the hook has a diameter of 1 inch

No Apology: The Case for American Greatness

On his first presidential visit to address the European nations, President Obama felt it necessary to apologize for America’s international po... Read More >

More Sideways Arithmetic From Wayside SchoolA sequel to the original collection of brain twisters and math puzzles includes ""How much is PEPPERS + PIG LIPS?"" and ""If Jenny's shirt has sparkles on it, will Todd have egg in his hair?""

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

Bookmark to:

Hide Sites

2 Responses to “SQL SELECT CASE Instead of IIF”