How to write IF...THEN in an SQL query? (sql server)



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.