Wednesday, 6 March 2013

SQL Server 2012: IIF

SQL Server 2012 introduces the “IIF” function, it’s a shorthand for the CASE expression. I am sure C# developers would love it!

An example: here we are fetching the columns in the table (HumanResources.Employee) using sys.columns and sys.types

> Using CASE

USE
AdventureWorks2012
GO

SELECT      col.[Name], 
CASE col.[is_nullable] WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable],
CASE col.[is_identity] WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsIdentity],
types.[name] as [DataType],
col.[max_length] AS [MaxLength]
FROM sys.columns col
INNER JOIN sys.types types ON
col.[user_type_id] = types.[user_type_id]
WHERE col.[object_id] = object_id('HumanResources.Employee')
ORDER BY col.[NAME]
GO

> Using IIF
SELECT col.[Name], 
IIF(col.[is_nullable] = 1, 'Yes', 'No') AS [IsNullable],
IIF(col.[is_identity] = 1, 'Yes', 'No') AS [IsIdentity]
,
types.[name] as [DataType],
col.[max_length] AS [MaxLength]
FROM sys.columns col
INNER JOIN sys.types types ON
col.[user_type_id] = types.[user_type_id]
WHERE col.[object_id] = object_id('HumanResources.Employee')
ORDER BY col.[NAME]
GO

> Output of the two queries

image

No comments:

Post a Comment

Note: only a member of this blog may post a comment.