Logical Function in SQL Server 2012

IIF() Function

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

IIF () Function Syntax

IIF(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 index is numeric it will convert to integer , and if the index is great than the element 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;