New Logical Function IIF in SQL Server 2012
IIF is one of the new logical functions in SQL Server 2012 which Returns one of two values, depending on whether the Boolean expression evaluates to true or false.
Generally we had to use IF condition or CASE….WHEN….THEN statement to achieve the functionality being offered by IIF. IIF is shorter function to use as against IF condition and CASE….WHEN….THEN statement. If you write IIF function compiler actually rewrites the IIF function as a CASE statement so the behavior of IIF and the simple CASE statement are identical though you will save some key stroke as compare with CASE…WHEN.
Let us see one small example which demonstrates the use of IIF function in SQL Server 2012.
IF OBJECT_ID('tempdb..#FunctionTesting') IS NOT NULL DROP TABLE #FunctionTesting CREATE TABLE #FunctionTesting ( ID INT IDENTITY(1,1) ,EmpName VARCHAR(50) ,NightShiftDay INT ) INSERT INTO #FunctionTesting VALUES ('Ritesh Shah',1) ,('Rajan Shah',2) ,('Bihag Thakar',3) ,('Kavan Dhruv',4) ,('Paresh Prajapati',5) ,('Kalu Bhuva',6) ,('Prapa Acharya',7) ,('Bhushan Shah',1) --Use of IIF SELECT IIF(NightShiftDay=1 or NightShiftDay=7,'WeekendNight','RegularDayNight' ) AS ShiftDetail ,EmpName FROM #FunctionTesting --used to achieve the same with CASE....WHEN since long SELECT CASE WHEN NightShiftDay=1 or NightShiftDay=7 THEN 'WeekendNight' ELSE 'RegulardayNight' END AS ShiftDetail ,EmpName FROM #FunctionTesting
Here is the output of both SELECT statements
I have earlier written few articles to demonstrate few different SQL Server function, you can refer those articles from the table given below:
|Function||Link to learn|
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.