“IIF” function in SQL Server

In SQL Server, the IIF function is a short-hand way for writing a case expression. This function returns one of the two values, depending on whether the boolean expression evaluates to True or False.

To understand the working of the IIF function- Let’s consider a table called devices having three columns device_id, device_name, and device_status.

select * from devices;

Problem statement:- Write an sql query to get device_id, device_name, and device_status when device_status=1 then print device_status as online and when device_status=0 then print device-status as offline from table devices.

For this problem statement, we have two solutions:-

1. By using a CASE statement

2. By using the IIF function

Implementation:-

  1. By using a CASE Statement

select device_id, device_name, case when device_status=1 then ‘Online’ else ‘Offline’ end as device_status from devices;

By using a CASE statement

2. By using the IIF function

select device_id, device_name, IIF(device_status =1, ‘Online’,’Offline’) as device_status from devices;

By using the IIF function

Thanks for the read. Do clap if find useful.🙂

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻