Some more new functions for SQL Server 2012


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)


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 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’


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
select object_id, name, type_desc
from sys.tables

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

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