Introduction

In this tutorial we are going to explain how to wirte an IF...THEN statement with SQL Server. In our example queries we have a table named User and we want to return 1 when the user is is_active or is_admin.

First approach using CASE

You can use the CASE statement in SQL and is supported on all versions of SQL Server

SELECT CASE WHEN is_active = 'Y' or is_admin = 'Y' THEN 1 ELSE 0 END AS Usable, * FROM User 

The query will return an integer, you may want to return a boolean in that case you will need to do a cast.

Also you can use a CASE embedded in other CASE and even included in aggregates.

Using IIF statement

With SQL Server 2012 it was introducesd the IIF statement instead of CASE.

SELECT IIF(is_admin = 'Y' OR is_active = 'Y', 1, 0) AS Usable, * FROM User 

The pro of using IIFF is simplicity or a short query, but IIF is not SQL standard.