IIF to Case statement

Question

I have the below code in Access which I'm migrating to SQL Server,

 IIf([code] Is Not Null,IIf([code]<>'ABC',

 IIf([length] Is Null,1,IIf([Length]=0,1,

 IIf([width] Is Null,1,IIf([Width]=0,1,

 IIf([height] Is Null,1,IIf([Height]=0,1,

 0))))))))

I believe it checks for code not null and not equal to 'ABC' then length should be null or 0 then it assigns a value 1 of the condition fails then it is 0. I need a little help with writing it with case.

Answer 1

Literal translating:

CASE WHEN [code] Is Not Null
THEN 
    CASE WHEN [code]<>'ABC'
    THEN
        CASE WHEN [length] Is Null 
        THEN 1
        ELSE 
            CASE WHEN [Length]=0
            THEN 1
            ELSE
                CASE WHEN [width] Is Null
                THEN 1 
                ELSE 
                    CASE WHEN [Width]=0
                    THEN 1
                    ELSE
                        CASE WHEN [height] Is Null
                        THEN 1
                        ELSE
                            CASE WHEN [Height]=0
                            THEN 1
                            ELSE 0
                            END
                        END
                    END
                END
            END
        END
    END
END

Simplified in :

CASE WHEN ISNULL([code], 'ABC') <>'ABC' AND
    (ISNULL([length], 0) = 0 OR
     ISNULL([width], 0) = 0 OR
     ISNULL([height], 0) = 0)
THEN 1
ELSE 0
END

Answer 2

IIf in JET SQL translates to CASE WHEN in SQL Server as follows:

IIf(condition, whenTrue, whenFalse)

translates directly to

CASE WHEN condition THEN whenTrue ELSE whenFalse END

For nested expressions

IIf(condition1, whenTrue1, IIf(condition2, whenTrue2, whenFalse))

you can either translate them directly:

CASE WHEN condition1 
     THEN whenTrue1 
     ELSE CASE WHEN condition2
               THEN whenTrue2
               ELSE whenFalse
          END
END

or use the fact that CASE allows you to specify multiple WHEN conditions:

CASE WHEN condition1 THEN whenTrue1 
     WHEN condition2 THEN whenTrue2
     ELSE whenFalse
END

With this knowledge, translating your statement should be easy and is left as an exercise to the reader (I don't want to spoil the fun).