SQL SELECT CASE Instead of IIF

E05C0037

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 CURSOR Performance – SQL For ProgrammersSQL CURSOR Performance – SQL For Programmers Last week I showed how to use the SQL CURSOR to loop through records in a database.  In that article I mentioned that you want to avoid using the CURSOR if you can because it has performance p...
  • SQL For Programmers – Finding a StringSQL For Programmers – Finding a String Many times in our queries, we aren't looking for an exact match.  We are looking for one string that exists in another.  There are a couple statements available to us that will allow us to do this...
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we want to fin...
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that don't really ...
  • SQL For Programmers – ALTERing the TABLESQL For Programmers – ALTERing the TABLE One of the main problems we seem to be stuck with at this point in programming history is, how do we version our databases? You could put the whole database into version control, I guess.  Bu...
  • http://osusports.info Steve

    Is there a performance difference?

  • Mitch

    Yeah. IIF doesn’t perform at all. You HAVE TO use SELECT CASE if you want to do this as part of a SELECT statement in TSQL.