Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

IIF function in SQL Server

Suggested Videos
Part 119 - UNPIVOT in SQL Server
Part 120 - Reverse PIVOT table in SQL Server
Part 121 - Choose function in SQL Server



In this video we will discuss IIF function in SQL Server.



IIF function
  • Introduced in SQL Server 2012
  • Returns one of two the values, depending on whether the Boolean expression evaluates to true or false
  • IIF is a shorthand way for writing a CASE expression
Syntax : IIF ( boolean_expression, true_value, false_value )

Example : Returns Male as the boolean expression evaluates to TRUE

DECLARE @Gender INT
SET @Gender = 1
SELECT IIF( @Gender = 1, 'Male', 'Femlae') AS Gender

Output : 
iif function in sql server example

Example : Using IIF() function with table data. We will use the following Employees table for this example.
sql server iif function example

SQL Script to create Employees table
Create table Employees
(
     Id int primary key identity,
     Name nvarchar(10),
     GenderId int
)
Go

Insert into Employees values ('Mark', 1)
Insert into Employees values ('John', 1)
Insert into Employees values ('Amy', 2)
Insert into Employees values ('Ben', 1)
Insert into Employees values ('Sara', 2)
Insert into Employees values ('David', 1)
Go

Write a query to display Gender along with employee Name and GenderId. We can achieve this either by using CASE or IIF.
iif function in sql server 2012

Using CASE statement
SELECT Name, GenderId,
        CASE WHEN GenderId = 1
                      THEN 'Male'
                      ELSE 'Female'
                   END AS Gender
FROM Employees

Using IIF function
SELECT Name, GenderId, IIF(GenderId = 1, 'Male', 'Female') AS Gender
FROM Employees

8 comments:

  1. Hi Venkat sir,

    Can you upload videos on SQL Jobs?

    Thank you!

    ReplyDelete
  2. What are difference between Truncate and Delete and Drop in SQL Server ?

    ReplyDelete
    Replies
    1. 1:Truncate is DDL Command where as Delete is DMl Commant.
      2:Delete command can be delete all or selected data from a table with where condition where as Truncate delete the all data from a table and we can use filter criteria in truncate.
      3: Truncate is much faster than delete command
      4:Trunacte command reset the identity column where as delete command cant be reset the identity column.

      Delete
    2. 1:Drop is DDL Command
      2:Drop is used fro drop the table

      Delete
    3. THE main difference in between Truncate and Delete is:
      in case of delete we can use rollback stateent the data
      but in case of truncate it's not possible to use rollback the data.

      Delete
  3. Truncate:Delete all Rows from Table

    Truncate table tablename

    Delete:Delete Row BY Row From Table Or You Can Also Delete All Row From Table
    Delete from tblename
    delete from tablename where id=1

    Drop:Delete Table Schema Means Delete
    drop table table name

    ReplyDelete
  4. Venkat; You are a real gift from God, the Mighty. I have never seen a real generous knowledge source like you. Thank you stay blessed.

    ReplyDelete
  5. Can you please create a video using IFF or IF in UPDATE query?
    It will be a big help.
    Thank you

    ReplyDelete

It would be great if you can help share these free resources