RSS

Logical Function in SQL Server 2012

13 Jan

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;

 
Leave a comment

Posted by on January 13, 2012 in New Function

 

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