IIF statement in SQL Server 2005

Question

Is there an IIF statement in the entire version of SQL Server?

I checked the 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 get the error "Invalid syntax near s →".

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

Answer 1

This IIF statement exists only in MDX , the query language for SQL Server Analysis Services, the data side for storing SQL Server data.

Normal T-SQL has no IIF statement.

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

Answer 2

You better use a CASE expression:

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

Also note that I have included the date character in a safe format - "2010/12/2" can be interpreted by the SQL server on February 12th and December 2nd.

Answer 3

  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%]