In MS SQL Server, we can use a CASE statement to implement IF…THEN. Here is the example of the CASE statement.
If your logic is as follows:
IF 1 > -1
THEN
‘TRUE’
ELSE
‘FALSE’
You can use CASE statement as follows:
— SQL Server 2008 and earlier version solution
SELECT CASE WHEN -1 < 1 THEN ‘TRUE’ ELSE ‘FALSE’ END AS Result
GO
A New function “IIF” has introduced in MS SQL 2012. This function is shorthand way for writing CASE statement. These functions take three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result.
— SQL Server 2012 solution
Example 1: IIF Usage
SELECT IIF ( -1 < 1, ‘TRUE’, ‘FALSE’ ) AS Result;
GO
Example 3: IIF with NULL
DECLARE @Variable INT=NULL
SELECT IIF ( @Variable IS NULL, ‘Value is Null’, ‘Value is NOT NULL’ ) AS Result;
GO
Example 2: Nested IIF
SELECT IIF ( 1 > -1, IIF ( 1 = 1, ‘Inner True’, ‘Inner False’ ), ‘FALSE’ ) AS Result;
In above example, we can see how IIF is used instead of CASE statement. IIF can be used the same way as CASE statement in SELECT statement.
