Thursday, May 17, 2012

SQL Server 2012 : Choose Function, IIF Function

Microsoft introduced two new logical functions in SQL Server 2012.
1. Choose
2. IIF

The Choose function returns the item based at specified index from a given list.

The IIF Function returns one or more values based on a given logical expression.


Choose ( index, value1, value2, value3......)

select choose (2, 'kalyan','kumar','akula')   -- Returns "kumar"

Select Choose(Product_Number, 'SQL 2000', 'SQL 2005', 'SQL 2008', 'SQL 2008 R2', 'SQL 2012')
from product


IIF ( boolean, true, false)

Select IIF(datename(dw,getdate())='sunday','Today is Sunday', Not A Holiday')

SELECT IIF((datename(dw,getdate()) in ('Saturday','Sunday')), 'Oncall',
               IIF((datename(dw,getdate()) in ('Monday')), 'Incident Management',
               IIF((datename(dw,getdate()) in ('Tuesday')), 'Release Management',
               IIF((datename(dw,getdate()) in ('Wednesday')), 'Change Management',
               IIF((datename(dw,getdate()) in ('Thursday')), 'Problem Managemnt',
               IIF((datename(dw,getdate()) in ('Firday')), 'Documentation', 'Enjoy Holiday'))))))

No comments: