New Logical functions coming in SQL Server Denali CTP3

As I have started to learn new functions arrived by SQL Server Denali CTP3 version and I have posted some of them. You can learn Analytical functions , Conversion functions and String functions. You can also get the list of all the features coming in SQL Server 2011 CTP3.

Now diverting on this post for the new Logical functions which are following,

1. IIF : It returns one of two arguments, depending on the evaluation of expression. It has require three arguments, first is condition, second and third are the values. Depending on the evaluation of first condition second or third values will be returned, means if the first condition is true, then it will return second value and if it is false then it will return third value. It returns the data type with the highest precedence from the types in true_value and false_value. Let us evaluate it to know it better.

DECLARE @IsDone bit
SET @IsDone = 1
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Above code returns "Success". Now les us assign the NULL value to variable then verify.

DECLARE @IsDone bit
SET @IsDone = NULL
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Here it returns "Failed" as output. Now elaborate it with more examples.

DECLARE @IsDone bit
DECLARE @FirstVal varchar(10)
DECLARE @SecondVal varchar(10)
SET @IsDone = 1
SET @FirstVal = NULL
SET @SecondVal = NULL

SELECT iif(@IsDone = 1,@FirstVal, @SecondVal)
GO

Above code returns NULL as resulted output. What happen if we pass directly NULL in both first and second values?

DECLARE @IsDone bit
SET @IsDone = 1

SELECT iif(@IsDone = 1,NULL, NULL)
GO

It comes with following error,

"Msg 8133, Level 16, State 1, Line 4
At lease one of the result expressions in a CASE specification must be an expression other than the NULL constant."

Now second turns come for CHOOSE function.

2. CHOOSE : It returns the value at the specified index from among the lists. It has require the first argument as the Index and hen we can pass multiple parameters for the values. It returns the data type with the highest precedence from the set of types passed to the function. Let elaborate it with sample examples.

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,'First','Second','Third')
GO

It returns "Second" value because it belongs to the second index.

DECLARE @Index int
SET @Index = 0

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Above query returns NULL as output.

DECLARE @Index int
SET @Index = NULL

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Same as earlier query it also returns NULL as resulted output. What happen if we pass all the values with NULL?

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,NULL,NULL,NULL)
GO

It's also come up with a NULL. Hope you liked these functions. Stay tuned for more posts.