Monday, December 2, 2013

IIF Statement (Alternate for CASE statement)

SQL Server 2012 includes some new T-SQL features. I would like to talk about 
one such feature called "IIF Statement". 

This is a simplied version of CASE statement. Results from both
CASE and IIF would be same, but with a minimized code, mostly in those occasions where we need to write nested CASE statements.

SQL Server Reporting Services Developers might be aware of this function while developing customized color expressions etc., Now lets see how it works
in T-SQL.

IIF Syntax:
IIF(Condition, True, False)

E.g 1:
DECLARE @X INT = 23
DECLARE @Y INT = 45
SELECT IIF(@X < @Y, 'True', 'False') AS IIF_Result;

Result Set:




E.g 2:
Lets see how it works on few records of a table:
Query:
SELECT 
IIF (JOB IN ('SALESMAN', 'CLERK'), 'LOW CADRE', 'HIGH CADRE')
FROM [dbo].[EMP]



Summary: IIF function is surely a good approach to handle the business logics where we need write Nested CASE statements. Hope this is useful! Happy TSQLing :)



1 comment:


  1. Nice Article !

    Really this will really help to people of SQL Server Community.
    I have also prepared small note on this, basic detail of new logical function IIF of SQL Server 2012

    http://www.dbrnd.com/2015/12/sql-server-2012-introduce-new-logical-fuction-iif/

    ReplyDelete