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.
"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.
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') GOAbove 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') GOHere 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) GOAbove 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) GOIt 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') GOIt returns "Second" value because it belongs to the second index.
DECLARE @Index int SET @Index = 0 SELECT CHOOSE (@Index,'First','Second','Third') GOAbove query returns NULL as output.
DECLARE @Index int SET @Index = NULL SELECT CHOOSE (@Index,'First','Second','Third') GOSame 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) GOIt's also come up with a NULL. Hope you liked these functions. Stay tuned for more posts.
Comments
Post a Comment