SQL 2012 – New Functions

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: