New Logical Function IIF in SQL Server 2012

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
LEAD Click Here
LEG Click Here
CUME_DIST Click Here
CHOOSE Click Here
PERCENT_RANK Click Here
LAST_VALUE Click Here
FIRST_VALUE Click Here
EOMONTH Click Here
DATEADD Click Here
DATEFROMPARTS Click Here
DATETIMEFROMPARTS Click Here
TIMEFROMPARTS Click Here
PARSE Click Here
TRY_PARSE Click Here
TRY_CONVERT Click Here
ISNULL Click Here
COALESCE Click Here
CONCAT_NULL_YIELDS_NULL Click Here

 

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.