SQL SELECT CASE Instead of IIF
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 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
- 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 for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014