How to perform an IF…THEN operation in SQL SELECT statement?

In SQL Server there is an IF…ELSE control flow statement. However, it cannot be used inside a SELECT statement. The closest of IF…THEN operation which can be used in SELECT statements is CASE expression or the IIF function. Let us see how to use CASE and IIF using an example.

For this illustration, let us create a table and insert some records.

/* Creating a table */
CREATE TABLE [dbo].[MTB_Sample](
[OrderNumber] [int] IDENTITY(1,1) NOT NULL,
[OrderItem] [varchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

/* Insert Records */
insert into MTB_Sample values ('Item One','2022-01-01')
insert into MTB_Sample values ('Item Two','2022-01-05')
insert into MTB_Sample values ('Item Three','2022-02-08')
insert into MTB_Sample values ('Item Four','2022-02-21')
insert into MTB_Sample values ('Item Five','2022-03-03')
insert into MTB_Sample values ('Item Six','2022-03-11')
GO

/* Check the data */
Select * from MTB_Sample
GO

With this table, we will create a select statement. In the select statement, if the OrderDate is greater than or equal to 2022/03/01, then we have to show the order as Current, else Old. We will try to achieve this using two methods CASE and IIF

CASE expression

Here is the SELECT statement to identify the orders whether they are current or old using the CASE expression.

SELECT *, 
	CASE
        WHEN OrderDate >= '2022-03-01'
            THEN 'Current Order'
            ELSE 'Old Order'
    END as OrderType
FROM MTB_Sample;

IIF function

IIF function is just the shorthand version of CASE expression introduced in SQL Server 2012. The above statement with CASE can be re-written with IIF to get the same result.

SELECT *, 
	IIF(OrderDate >= '2022-03-01', 'Current Order', 'Old Order')
FROM MTB_Sample;

Reference

• More about CASE expression at Microsoft Docs.
• More about IIF function at Microsoft Docs.