SQLServerLog.com

IIF() Logical Function in SQL Server

by · Published · Updated

IIF Logical Function

IIF() logical function has been introduced in SQL Server 2012. We already know about CASE expression and IF-Else statement in SQL Server so now you can also use IIF() function to get the same output in a short way. IIF() function’s execution plan is similar to CASE expression.

IIF() function returns one correct value based on true or false boolean expression values.

Syntax:

IIF ( boolean_expression, first_value, second_value )

IIF() function takes three arguments, first is boolean expression. When boolean expression evaluates to true then first value is the output and when boolean expression evaluates to false then second value is the output. Out of two values, highest precedence is checked and it returns the data type. We will see how to use IIF() function with examples.

Example 1:

SELECT IIF(99 > 100, 'TRUE', 'FALSE') AS OUTPUT 
GO

Here 99 is not greater than 100 so it will return second value as output which is FALSE.

Example 2:

DECLARE @STUDENT VARCHAR(100) = 'Siddiqui'
SELECT IIF(@STUDENT IN ('Manzoor', 'Siddiqui'), 'DBA', 'TechWriter') AS OUTPUT
GO

Here we are assigning and comparing string value and as assigned string value results in true so first value ‘DBA’ is the output.

Example 3:

DECLARE @gender1 VARCHAR(20) = 'M', @gender2 VARCHAR(20) = 'F', @gender3 VARCHAR(20) = 'X'
SELECT IIF(@gender1 = @gender2, 'MALE', IIF(@gender2 = @gender3, 'FEMALE', 'X')) AS Gender
GO

Here we are checking nested IIF() function to find the gender, it will return ‘X ‘as output.

Example 4:

DECLARE @valueX INT = 99, @valueY INT = 100;
SELECT IIF ( @valueX < @valueY, 'TRUE', 'FALSE') AS Output
GO

Here integer value X is less than value Y so it will return TRUE.

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

You may also like...

Leave a Reply