SQL Server视图SELECT语句中IIF函数报错，求修正后的查询

问题说明 #

编写的SQL Server视图SELECT语句因系统无法识别IIF函数内的=运算符报错，移除IIF后可正常运行。报错信息：

Error in list of function arguments: '=' not recognized. Unable to parse query text.

原查询语句：

SELECT        
    dbo.inventory.price, 
    dbo.product_price_multiplier.multiplier AS mult, 
    dbo.product_price_multiplier.item_number,  
    dbo.product_price_multiplier.width, 
    dbo.product_price_multiplier.length, 
    dbo.product_price_multiplier.width / 12 * dbo.product_price_multiplier.length / 12 AS units, 
    IIF (dbo.product_price_multiplier.length = 0.000, 
         dbo.inventory.price * dbo.product_price_multiplier.multiplier,
         (dbo.inventory.price * dbo.product_price_multiplier.multiplier) * (dbo.product_price_multiplier.width / 12 * dbo.product_price_multiplier.length / 12)) AS base_price
FROM     
    dbo.product_price_multiplier 
RIGHT OUTER JOIN
    dbo.inventory ON dbo.product_price_multiplier.item_number = dbo.inventory.item_number 
                  AND dbo.product_price_multiplier.length = dbo.inventory.length 
                  AND dbo.product_price_multiplier.width = dbo.inventory.width

修正后的查询语句 #

报错原因大概率是所用SQL Server版本低于2012（IIF函数是SQL Server 2012才引入的），换成兼容性更强的CASE WHEN语句即可解决：

SELECT        
    dbo.inventory.price, 
    dbo.product_price_multiplier.multiplier AS mult, 
    dbo.product_price_multiplier.item_number,  
    dbo.product_price_multiplier.width, 
    dbo.product_price_multiplier.length, 
    dbo.product_price_multiplier.width / 12 * dbo.product_price_multiplier.length / 12 AS units, 
    CASE 
        WHEN dbo.product_price_multiplier.length = 0.000 
            THEN dbo.inventory.price * dbo.product_price_multiplier.multiplier
            ELSE (dbo.inventory.price * dbo.product_price_multiplier.multiplier) * (dbo.product_price_multiplier.width / 12 * dbo.product_price_multiplier.length / 12)
    END AS base_price
FROM     
    dbo.product_price_multiplier 
RIGHT OUTER JOIN
    dbo.inventory ON dbo.product_price_multiplier.item_number = dbo.inventory.item_number 
                  AND dbo.product_price_multiplier.length = dbo.inventory.length 
                  AND dbo.product_price_multiplier.width = dbo.inventory.width

如果你的SQL Server版本是2012及以上，也可以尝试调整IIF函数的空格（确保=前后有空格、函数参数分隔符正确），但CASE WHEN的兼容性更可靠。

内容的提问来源于stack exchange，提问作者duerzd696