SQL SELECT CASE Instead of IIF

June 2013
S	M	T	W	T	F	S
« May
	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

October 20th, 2009 |

Author: Dave

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

Other post in SQL For Programmers

SQL For Developers - 9 Reasons to bother - August 13th, 2008
MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
SQL For Programmers - ALTERing the TABLE - September 1st, 2008
SQL For Programmers - Finding a String - September 3rd, 2008
SQL For Programmers - Finding IN a List - September 8th, 2008
SQL For Programmers - Stored Procedure Basics - September 16th, 2008
Basic SQL Commands - SQL For Programmers - September 18th, 2008
SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
SQL WHILE - SQL For Programmers - October 2nd, 2008
Temporary Tables - SQL For Programmers - October 7th, 2008
SQL CURSOR - SQL For Programmers - October 13th, 2008
SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
Random in SQL - SQL For Programmers - November 4th, 2008
SQL - Filtering WHERE condition on two rows - November 26th, 2008
SQL - Transactions - April 15th, 2009
SQL For Programmers – New Question - July 6th, 2009
SQL SELECT CASE Instead of IIF - October 20th, 2009
SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012

SQL 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 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 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 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 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...

Posted in SQL For Programmers |

Tags: case, else, iif, sql, tsql, when

.NET Answers

SQL SELECT CASE Instead of IIF

Related Post