mssql which is the syntax 'if'

Question

I have this code in mssql:

SELECT
    t1.Id,
    t2.Id,
    t1.QuantityIn,
    t1.PriceIn,
    t2.QuantityOut,

    (If (t2.QuantityOut - t1.QuantityIn)=0

        THEN t2.QuantityOut

    Else t2.QuantityOut - t1.QuantityIn ) AS Quant,

    t2.PriceOut

FROM t1

LEFT JOIN t2 ON t2.Id = t1.Id

In software MsSql Server Management Studio error is

Incorrect syntax near the keyword 'If'.

What is the correct syntax for 'if' in my case?

Thank you!

@lad2025's answer is correct for SQL Server 2012+, but it is more portable to use a `CASE` statement. https://stackoverflow.com/a/22839537/2084315 — ps2goat, Sep 28 '17 at 18:46

score 1 · Answer 1 · answered Sep 28 '17 at 18:43

1

You need to use IIF (SQL Server 2012 and above):

IIF ( boolean_expression, true_value, false_value )

SELECT
    t1.Id,
    t2.Id,
    t1.QuantityIn,
    t1.PriceIn,
    t2.QuantityOut,
    IIF (t2.QuantityOut - t1.QuantityIn=0,t2.QuantityOut
        , t2.QuantityOut - t1.QuantityIn ) AS Quant,
    t2.PriceOut
FROM t1
LEFT JOIN t2 ON t2.Id = t1.Id

answered Sep 28 '17 at 18:43

Lukasz Szozda

162,964
23
234
275

IIF you have SQL Server 2012+ – Tab Alleman Sep 28 '17 at 18:44

score 1 · Answer 2 · answered Sep 28 '17 at 18:47

Try this

SELECT
    t1.Id,
    t2.Id,
    t1.QuantityIn,
    t1.PriceIn,
    t2.QuantityOut,

    CASE WHEN t2.QuantityOut - t1.QuantityIn =0

        THEN t2.QuantityOut

    Else t2.QuantityOut - t1.QuantityIn END AS Quant,

    t2.PriceOut

FROM t1

LEFT JOIN t2 ON t2.Id = t1.Id

mssql which is the syntax 'if'

2 Answers2