Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher  - SCHEDULE CALL

sddsfsf

Detailed Overview of IIF, Choose & Case Statement

 

IIF is one of the logical Transact-SQL functions that returns one among the two values based on the boolean expression. On the other hand, CASE is a language expression that assesses a list of conditions and returns one among multiple values based on the boolean expression. IIF is for single if-else conditions, and the case statement is for multiple conditions. There is a third function called Choose in SQL Server(). This returns the item from a list of items at a specified index. Let us cover each statement in detail, be it iff sql, switch sql, or case statement in SQL Server.

What is IIF in SQL Server?

IIF() function in SQL Server returns one of two values, be conditional on whether the Boolean expression assesses to true or false in SQL Server.

SQL Server IIF syntax

The syntax for iif in SQL is as follows

IIF(condition, value_if_true, value_if_false)

Advantage of SQL Server IIF

The IIF() function always returns a value if the condition is TRUE, or another value if the condition is FALSE.

Disadvantage of SQL Server IIF

As mentioned, the IIF() function is based on the CASE expression. It, therefore, has the same limitations as the CASE expression (such as only being able to nest to a maximum level of 10).

Use of SQL Server IIF

The SQL IIF() function returns a value if the condition is TRUE or another value if the condition is FALSE.

IIF Statement in SQL Server Example

Let us take the case of the Adventurework database. The table that we will be using for this particular example is Employee.

Below is the query to view the table data

select * from [HumanResources].[Employee]

and below is the output

There is a field that holds the marital status of a particular employee. In the case of married, it shows M, and in the case of Single and S.

We will write a query using the SQL IIF statement, showing married if it is M and Single if it finds S. The query looks below.

Select Emp.BusinessEntityID,
Emp.NationalIDNumber,
Emp.LoginID,
Emp.OrganizationNode,
Emp.OrganizationLevel,
Emp.JobTitle
emp.BirthDate,
iif(emp.MaritalStatus='M', 'Married,' 'Single') MaritalStatus
 from [HumanResources].[Employee] Emp

The output looks like the below

SQL Server Switch Case

The CASE statement goes through various conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is correct, it will stop reading further and return the result. If no conditions are correct, it returns the value in the ELSE clause.

If no ELSE part exists and no conditions are proper, it returns NULL.

SQL Server Switch Statement Syntax

The SQL Switch case syntax is as below

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition THEN result
ELSE result
END;

Advantage of Switch in SQL Server

The use of CASE tools results in goodish enhancements to quality. This can be primarily thanks to the fact that one can effortlessly retell through the various phases of code development, and therefore the possibilities of human error are significantly reduced.

Performance of Switch in SQL Server

Queries with multiple case statements encounters performance issues, as using multiple case statements tends to slow the query.

SQL Server Switch Case Statement

Let us take the case of the Adventurework database. The table that we will be using for this particular example is Employee.

Below is the query to view the table data

select * from [HumanResources].[Employee]

and below is the output

There is a field that holds the marital status of a particular employee. In the case of married, it shows M, and in the case of Single and S.

We will write a query using the IIF statement, showing married if it is M and Single if it finds S. The query looks below.

Select Emp.BusinessEntityID,
Emp.NationalIDNumber,
Emp.LoginID,
Emp.OrganizationNode,
Emp.OrganizationLevel,
Emp.JobTitle,
emp.BirthDate,
case
when Emp.MaritalStatus='M' then 'Married'
when Emp.MaritalStatus='S' then 'Single'
End [MaritalStatus]
from [HumanResources].[Employee] Emp

The output looks like below

Choose SQL Server Function

We use Choose () function to return an item at a particular index position from the list of items. Syntax of Choose function: CHOOSE ( index, value[1], value[2] ,….. value[N] ) Index: It is an integer that specifies the index position of the element we want in the output.

SQL Server Choose Syntax

The syntax for the SQL Server Choose function is as below

CHOOSE ( index, val_1, val_2 [, val_n ] )  

Choose an SQL Server to Use

CHOOSE statement acts like an index into an array, where the array contains the arguments that follow the index argument. The index argument makes sure which of the following values will be returned.

SQL Choose Function Example

Following is the SQL query for the sample choose function

SELECT CHOOSE ( 2, 'Microsoft,' 'SQL', 'Server') AS Output;

The output looks like below

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Conclusion

The blog documents the three most common conditional functions of SQL Server, iif, case, and chose. It describes its advantage and disadvantage with proper examples and syntax. It also shows how to use these tools with live examples. This reading will interest those who want to learn more about these three topics.

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

-1 day 05 Nov 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

6 days 12 Nov 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

5 days 11 Nov 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

-1 day 05 Nov 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

9 days 15 Nov 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

2 days 08 Nov 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

1 day 07 Nov 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

2 days 08 Nov 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

3 days 09 Nov 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

2 days 08 Nov 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

9 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

2 days 08 Nov 2024