IIF statement in SQL Server 2005

Question

Does IIF statement exists in all version of SQL Server ?

I have checked a tutorial on MSDN.

But when I tried to run this code on my machine

DECLARE @newDate datetime
SET @newDate =  CONVERT(varchar, {fn NOW()}, 111)
SELECT IIF(@newDate > '2010/12/2', 'Greater', 'smaller')

But I am getting error of "Incorrect syntax near '>'."

Can someone provide me an example in SQL Server 2005 for the existence of the IIF statement?

score 11 · Accepted Answer · answered Dec 07 '10 at 09:00

11

That IIF statement only exists in MDX - the query language for SQL Server Analysis Services - the datawarehousing side of SQL Server.

Plain T-SQL does not have an IIF statement.

The best you can do in T-SQL is use the CASE.... WHEN... THEN... statement.

answered Dec 07 '10 at 09:00

marc_s

732,580
175
1,330
1,459

3

UPDATE: SQL Server 2012 now has an IIF Statement. http://msdn.microsoft.com/en-us/library/hh213574.aspx – unhappyCrackers1 Jun 13 '12 at 18:19
1

@Somantra: true - but that was not known back in Dec'2010, and the OP was asking about SQL Server 2005. – marc_s Jun 13 '12 at 18:20
3

The title says 2005, but his first question asked "Does IIF statement exists in all version of SQL Server ?". Hopefully someone finds something useful in this post in the future. ;-) – unhappyCrackers1 Jun 13 '12 at 18:29

score 7 · Answer 2 · answered Dec 07 '10 at 09:02

You're better off using a CASE expression:

DECLARE @newDate datetime
SET @newDate =  CONVERT(varchar, {fn NOW()}, 111)
SELECT CASE WHEN @newDate > '20101202' THEN 'Greater' ELSE 'smaller' END

Please also note that I've switched your date literal to a safe format - '2010/12/2' could be interpreted by SQL server as either the 12th February or 2nd December.

score -2 · Answer 3 · edited Nov 06 '13 at 05:04

   IIF([Add DVP Month].DevelopmentMonth>[Add DVP Month].COVMONTHS,
       1,
   IIF([STATUS]<>'1',
       1, 
   IIF([Add DVP Month].PLANTYPE = 'A' and [Add DVP Month].newUsedProgram = 'U' and [Add DVP Month].COVMONTHS = 60 and [Add DVP Month].COVMILES = 100000 and [Add DVP Month].postedDt >= #1/31/2010#,
   IIF([Add DVP Month].postedDt >= #1/31/2012#, 
       [EPMthd.PCM2],   
   IIF([Add DVP Month].postedDt >= #1/31/2010#, 
   [EPMthd.PCM1], 
   [EPMthd.PCM0])
   ),
   IIF([Add DVP Month].COVMONTHS = 999,[EPMthd.2],
   IIF([Add DVP Month].postedDt >= #1/31/2012#, [EPMthd.2],
   IIF([Add DVP Month].postedDt >= #1/31/2010#, [EPMthd.1],
   IIF([Add DVP Month].postedDt >= #1/31/2008#, 
   IIF([EPMthd.0] is null, 
       [EPMthd.8], 
       [EPMthd.0]
      ),
   IIF([Add DVP Month].postedDt < #1/31/2008#, 
   IIF([EPMthd.8] is null, 
   IIF([Add DVP Month].COVMONTHS = 0,0, 
       [Add DVP Month].DevelopmentMonth/[Add DVP Month].COVMONTHS
      ),
       [EPMthd.8]
    ),
   IIF([Add DVP Month].COVMONTHS = 0,
   0, 
       [Add DVP Month].DevelopmentMonth/[Add DVP Month].COVMONTHS
    )
   )
 ))))))
 ) AS [EP%]

Agree. It's not what was asked and totally different syntax to SQL Server — Fandango68, Jul 08 '14 at 05:14

IIF statement in SQL Server 2005

3 Answers3

Linked