Some more new functions for SQL Server 2012

    CHOOSE

CHOOSE will return the value from a comma separated list of values in the position indicated by the first value supplied:
select CHOOSE(3, 9, 8, 7, 6, 5)

returns 7 (The 3rd value in the list of values)

    IIF

The immediate if will evaluate a boolean expression and return the 2nd value if true, and the 3rd value if the expression is false.

declare @Value int = 31
select (IIF(@Value > 10, ‘Greater than 10’, ‘Less than or equal to 10’))
returns ‘Greater than 10’

    CONCAT

CONCAT will concatenate a comma delimited list of values into one string. If the input values aren’t strings, then an implicit conversion will take place. NULL values are converted to empty length strings.

select CONCAT(1, 2, 3, 4)
returns ‘1234’

select CONCAT(‘One’, null, 2, ‘3’)
returns ‘One23’

    EXECUTE WITH RESULT SETS

WITH RESULT SETS will let you redefine the output of a stored procedure, including renaming the columns. This can be useful when working with a stored procedure where you aren’t able to change the definition, or are concerned with breaking some existing mapping with result column names.

create procedure sp_testproc
as
select object_id, name, type_desc
from sys.tables
go

exec sp_testproc
with result sets ((
ObjectId int not null,
TableName varchar(128) not null,
TypeDescription varchar(20) not null
));
go

Will return the records from sys.tables will the columns named ObjectId, TableName and TypeDescription.