Tag Archives: SQL SERVER 2012

CHOOSE LOGICAL FUNCTION IN SQL SERVER 2012

CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

SYNTAX: CHOOSE ( index, val_1, val_2 [, val_n ] )

Example 1: CHOOSE Basic Example

SELECT CHOOSE(1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(2,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(4,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_1

Example 2: CHOOSE function with variables as index and values.

DECLARE @SeasonId INT = 2, @Season1 Varchar(10) = 'Spring',
 @Season2 Varchar(10) = 'Summer',@Season3 Varchar(10) = 'Autumn',
 @Season4 Varchar(10) = 'Winter'

SELECT CHOOSE(@SeasonId, @Season1, @Season2, @Season3, @Season4)

Result: Summer

Example 3: CHOOSE will return NULL if INDEX position is outside the range of values.

SELECT CHOOSE(-10,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(0,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(5,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_2

Example 4: If index value is numeric, it will be implicitly converted to INT.

SELECT CHOOSE(3.1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3.85,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_3

Example 5: CHOOSE function Return Datatype will be the one with highest precedence from the Datatypes of the list of values

SELECT CHOOSE(3, 40.58, 50, 60, 70)

Result: 60.00

Example 6: CHOOSE function with Index Type as Varchar

SELECT CHOOSE('TEST','Spring','Summer','Autumn','Winter')

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

SELECT CHOOSE('3','Spring','Summer','Autumn','Winter')

Result: Autumn

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

Please correct me, if my understanding is wrong. Comments are always welcome.

IIF() LOGICAL FUNCTION IN SQL SERVER 2012

IIF() is one of the new built-in logical function introduced as a Part of Sql Server 2012. IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.

IIF() function takes three arguments, first argument should be a Boolean expression otherwise it raises an exception. If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result. This functions result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

Syntax: IIF ( boolean_expression, true_value, false_value )

Now let us understand this function with examples:

Example 1: Comparing two Integer numbers

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )

Result: FALSE

Example 2: Comparing two Integer Variables

DECLARE @Marks INT = 60, @Minimum INT = 35
SELECT IIF(@Marks >= @Minimum, 'PASS', 'FAIL' )

Result: PASS

Example 3: Comparing Strings

DECLARE @NAME Varchar(50) = 'Basav'
SELECT IIF(@NAME IN ('Basav','Kalpana'), 
                    'Likes SQL', 'Likes ORACLE' )

Result: Likes SQL

Example 4: Result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

SELECT IIF(1 > 10, 1.5, 40)

Result: 40.0

Example 5: Nested IIF() Statement.

DECLARE @Percentage AS NUMERIC(5,2) = 71
SELECT IIF(@Percentage >= 70, 'Distinction', 
        IIF(@Percentage>=35 AND @Percentage<70, 'Pass', 'Fail'))

Result: Distinction

Note: As IIF() statement internally translates to an CASE statement, so the Max Nesting level is 10.

Example 6: IIF() with both return value arguments value as NULL constant

SELECT IIF(1 > 2, NULL, NULL)

Result:
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant

Example 7: IIF() with on of the return value argument value as NULL constant

SELECT IIF(1 > 2, NULL, 'NO')

Result: NO

Example 8: IIF() with both return value arguments are variables with value NULL

DECLARE @A INT = NULL, @B INT = NULL
SELECT IIF(1 > 2, @A, @B)

Result: NULL

You may also like to read other LOGICAL FUNCTION CHOOSE() introduced in SQL SERVER 2012

Please correct me, if my understanding is wrong. Comments are always welcome.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012

TRY_PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_PARSE function is Similar to PARSE function, but if PARSE function fails to convert the value throws an exception where as TRY_PARSE function returns a NULL value.

Important Note: TRY_PARSE function is not a native SQL SERVER function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

Syntax:  TRY_PARSE ( string_value AS data_type [ USING culture ] )

Parameter Details:

string_value : String expression which needs to be parsed.
data_type : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture : Optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.

Now let us understand this TRY_PARSE function with examples:

-- TRY_PARSE String to INT
SELECT TRY_PARSE('1000' AS INT) AS 'String to INT'
-- TRY_PARSE String to NUMERIC
SELECT TRY_PARSE('1000.06' AS NUMERIC(8,2)) 
           AS 'String to NUMERIC'
-- TRY_PARSE String to DATETIME
SELECT TRY_PARSE('05-18-2013' AS DATETIME) 
           AS 'String to DATETIME' 
-- TRY_PARSE String to DateTime
SELECT TRY_PARSE('2013/05/18' AS DATETIME) 
           AS 'String to DATETIME'
-- TRY_PARSE string value in the India date format to DATETIME
SELECT TRY_PARSE('18-05-2013' AS DATETIME using 'en-in') 
 AS 'String in the India date format to DATETIME'
-- TRY_PARSE string value is in the US currency format to Money 
SELECT TRY_PARSE('$2500' AS MONEY using 'en-US')
 AS 'String in the US currency format to MONEY'

Result:
TRY_PARSE_CONVERSION_FUNCTION_IN_SQL_SERVER_2012

Difference between PARSE and TRY_PARSE

Try to convert invalid value, in this case PARSE throws exception but TRY_PARSE returns NULL Value

SELECT PARSE('Basavaraj' as DATETIME) 'PARSE RESULT'
GO
SELECT TRY_PARSE('Basavaraj' as DATETIME) 'TRY_PARSE RESULT'

Result:

PARSE RESULT
———————–
Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘Basavaraj’ into data type datetime using culture ”.

TRY_PARSE RESULT
———————–
NULL

Below example demonstrate how we can check the result of TRY_PARSE function in IF condition:

IF  TRY_PARSE('Basavaraj' as DATETIME) IS NULL
           PRINT 'TRY_PARSE: Conversion Successful'
ELSE
           PRINT 'TRY_PARSE: Conversion Unsuccessful'

Result:
TRY_PARSE: Conversion Successful

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

PARSE CONVERSION FUNCTION IN SQL SERVER 2012

PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. PARSE function converts the string expression to the requested data type. It tries it’s best to translate the string value to requested type but if it fails to translate then raises an exception.

Important Note: This PARSE function is not a native Sql function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

Syntax:  PARSE ( string_value AS data_type [ USING culture ] )

Parameter Details:

string_value   : String expression which needs to be parsed.
data_type      : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture        : Optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.

Now let us understand this PARSE function with examples:

-- PARSE String to INT
SELECT PARSE('1000' AS INT) AS 'String to INT'
-- PARSE String to Numeric
SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'
-- PARSE String to DateTime
SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'
-- PARSE String to DateTime
SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'
-- PARSE string value in the India date format to DateTime 
SELECT PARSE('18-05-2013' as DATETIME using 'en-in') 
 AS 'String in Indian DateTime Format to DATETIME'
-- PARSE string value is in the US currency format to Money 
SELECT PARSE('$2500' as MONEY using 'en-US') 
 AS 'String in US Currency Format to MONEY'

RESULT:
PARSE_SQL_SERVER_2012_CONVERSION_FUNCTION

Below example demonstrates how PARSE function tries it’s best convert the input string value to  a specified data type even when the specified value is not in the correct format.

SELECT PARSE('08-JUNE-2013' AS DATETIME)
SELECT PARSE('08-JUN-2013' AS DATETIME)
SELECT PARSE('2013JUNE08' AS DATETIME)
SELECT PARSE('08/JUN/2013' AS DATETIME)

All the above statements will return the same result and it is: 2013-06-08 00:00:00.000

Now try to PARSE an invalid string value to DATETIME:

This conversion fails as February month will not have 31st day.

--PARSE invalid String to DATETIME
SELECT PARSE('2012/02/31' as DATETIME)
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘2012/02/31’ into data type datetime using culture ”.

Below PARSE function will fail to translate the date. Because the culture parameter specified is en-us, but the string value specified is not in US format. US Date format excepts month first instead of day and then day and year.

--PARSE invalid String to DateTime
SELECT PARSE('18-05-2013' as DATETIME using 'en-us')
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’18-05-2013′ into data type datetime using culture ‘en-us’.

Difference between PARSE and CONVERT function.

Below example demonstrates the difference between PARSE and CONVERT function. PARSE function will successfully converts the string ‘Saturday, 08 June 2013’ to date time, but the CONVERT function fails to convert the same value. That is PARSE function tries it’s best to convert the input string value to the requested type, but CONVERT function requires the input string to be exact format no variations allowed.

--PARSE Function Succeeds
SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Sat, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Saturday 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO

--CONVERT Function Fails
SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Sat, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Saturday 08 June 2013') 
                 AS 'CONVERT Function Result' GO

RESULT:

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

TRY_CONVERT CONVERSION FUNCTION IN SQL SERVER 2012

TRY_CONVERT is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_CONVERT function is similar to the CONVERT  function, but if CONVERT function fails to convert the value to the requested type then throws an exception where as TRY_CONVERT function returns NULL value.

Note:  TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted.

Syntax:  TRY_CONVERT (data_type [ ( length ) ], expression [, style ])

Parameter Details:

data_type     : The data type into which to cast expression.
expression  : The value to be cast.
style               : Optional integer value that specifies how the TRY_CONVERT function is to translate expression. The Values for this integer parameter are same as the one used by CONVERT function.

Now let us understand this TRY_CONVERT function with examples:

TRY_CONVERT – Succeeds

SELECT TRY_CONVERT(INT, '100')
SELECT TRY_CONVERT(NUMERIC(8,2), '1000.06')
SELECT TRY_CONVERT(INT, 100)
SELECT TRY_CONVERT(NUMERIC(8,2), 1000.06)
SELECT TRY_CONVERT(DATETIME, '05/18/2013')
SELECT TRY_CONVERT(DATETIME, '05/18/2013',111)

RESULT:

TRYCONVERTSUCCEEDS

TRY_CONVERT – Fails

In the below example we are trying to convert an invalid date i.e. 31st Feburary, 2013. In such cases TRY_CONVERT function will return NULL value instead of throwing exception.

SELECT TRY_CONVERT(DATETIME, '02/31/2013') 
            AS 'TRY_CONVERT Function Fails'

Result:
TRY_CONVERT Function Fails
————————–
NULL

TRY_CONVERT – Throws Exception

In the below example we are trying to convert an integer value to XML type which is not permitted explicitly, in such cases TRY_CONVERT function raises an exception.

SELECT TRY_CONVERT(XML, 10)

Result:
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

But on the other-hand conversion of an string value to an XML type is supported. Let us see this with an example:

SELECT TRY_CONVERT(XML, '10') AS 'XML Output'

Result:

XML Output
——————————-
10

Difference Between CONVERT and TRY_CONVERT

Both CONVERT and TRY_CONVERT function converts the expression to the requested type. But if the CONVERT function fails to convert the value to the requested type then raises an exception, on the other hand if TRY_CONVERT function returns a NULL value if it fails to convert the value to the requested type. Below example demonstrates this difference

SELECT CONVERT(DATETIME, '02/31/2013')
            AS 'CONVRT Function Result'
SELECT TRY_CONVERT(DATETIME, '02/31/2013') 
            AS 'TRY_CONVERT Function Result'

Result:
CONVRT Function Result
———————–
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

TRY_CONVERT Function Result
————————-
NULL

Below example demonstrate how we can check the result of TRY_CONVERT function in IF condition:

IF TRY_CONVERT(DATETIME,'Basavaraj') IS NULL
        PRINT 'TRY_CONVERT: Conversion Successful'
ELSE
        PRINT 'TRY_CONVERT: Conversion Unsuccessful'

Result:
TRY_CONVERT: Conversion Successful

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS