Category: SQL Server 2012

logical function in SQL Server 2012 RC0

IIF() Function

IIF () Function is great function and all developer know this function becouse its arealdy in VB.NET but the new it’s will be used it in SQL

IIF () Function Syantax

IFF(the Condition , ‘1’,’2′) if the condition is true the result will return no 1 , Else the result will return no 2

Examples

SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
—Example 2
DECLARE @NAME NVARCHAR(15)= ‘MOSTAFA’
SELECT IIF(LEN(@NAME) > 5 , ‘BIG NAME’ , ‘SHORT NAME’) AS LONG
—Example 3
DECLARE @VARIBALE INT =   NULL
SELECT IIF (@VARIBALE IS NULL
, ‘YES’,’NO’)
—Example 4
 CREATE TABLE EMPLOYEE
 (
 EMP_NAME NVARCHAR(50),
 CODE NVARCHAR(50)
 )
 INSERT INTO EMPLOYEE VALUES (‘MOSTAFA’,’M100′),(‘ABDEL-KAREEM’,’100′),(‘OMAR’,’O100′)
SELECT EMP_NAME,CODE,IIF(TRY_PARSE(CODE AS INT) IS NULL , ‘VALUE IS STRING’ , ‘VALUE IS NOT STRING’)
  FROM EMPLOYEE

CHOOSE() FUNCTION

Choose Function is very simple function if the indesx is numeric it will convert to integer , and if the index is great than the elemant in the list it will return NULL

Example NO 1

 SELECT CHOOSE ( 1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;
 SELECT CHOOSE ( 2, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;
 SELECT CHOOSE ( 3, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Third;

Example NO 2 (if the index is great than the elemant in the list )

SELECT CHOOSE ( 0, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Null;
 SELECT CHOOSE ( 4, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Result_NULL;

Example NO 3 ( if the indesx is numeric it will convert to integer )

 SELECT CHOOSE ( 1.1, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_First;
 SELECT CHOOSE ( 2.9, ‘TRUE’, ‘FALSE’, ‘Unknown’ ) AS Returns_Second;

• DATEFROMPARTS ( year, month, day)
• DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
• DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
• DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
• SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
• TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
• EOMONTH ()  — TO SHOW CND DATE IN MONTH

 set dateformat dmy
 select DATEFROMPARTS (1985,10,01)
 SELECT DATETIME2FROMPARTS (1985,10,1,11,31,00,0,0)
  SELECT DATETIMEFROMPARTS (1985,10,1,11,31,00,100)
 SELECT DATETIMEOFFSETFROMPARTS (1985,10,1,11,33,12,0,11,0,7)
 SELECT SMALLDATETIMEFROMPARTS (2010,12,31,23,59)
 SELECT TIMEFROMPARTS (23,59,59,0,0)
select EOMONTH (‘1985/10/1’)

Conversion Function

1- Parse()  function (can convert any string value to Numeric or Date/Time format.)

–Example1: Converting String to INT
SELECT PARSE(‘100.000’ AS INT) AS ValueInt
 select parse()

 –Example2: Converting String to Date/Time
SELECT PARSE(‘July 30, 2011’ AS DATETIME)
 AS ValueDT
 

2- Try_parse() Function

The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Example1: Converting String to INT
 
— No error
 SELECT PARSE(‘100.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘100.000’ AS INT) AS ValueInt;
 — Error
 SELECT PARSE(‘sa300.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘mostafa1985.10’ AS INT) AS ValueInt;

3-  TRY_CONVERT() function

This Function like Convert but convert function if you try to convert string to int it will result Error but TRY_COBVERT it will give you NULL like TRY_PARSE() Function .

—No Error
SELECT CONVERT(INT, ‘500’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘500’) AS ValueInt;
—Error
 SELECT CONVERT(INT, ‘m100.000’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘m100.000’) AS ValueInt;

Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

Diffrence Between pars() and Try_Pars() Function : Parse if try to use it with one or more incorrect values will throw an error However, if you use TRY_PARSE function it will not throw error but will return parse string to  the result as NULL

difference between Convert() and TRY_CONVERT() functions: Convert function try to convert the string and return the value if it can if it can’t it will return Error , However Try_Convert Function it will try to convert the string and return value if it can but if it can’t it will return NULL.