SQL Statement Equivalent to Ternary/Conditional Operator

Introduction: In this article I am going to explain how to use alternative of ternary or conditional operator which is not available in SQL Server.


Description: The conditional operator (? :) returns one of two values depending on the value of a Boolean expression. Syntax for the conditional operator is as follows:

condition ? first_expression : second_expression;

But there is no ternary/conditional operator available in sql server which was very useful.
While working with sql database I got the requirement as follows:

SELECT column1, (column2 == "any value"? abc : xyz) FROM table1

My table store employee gender as 'M' for Male and 'F' for Female but I want to display them as Male or Female. First idea came into my mind was to use ternary operator but as we know there is no ternary operator available in sql. So I have to find similar alternatives. I ended up with two alternative solutions. One is to achieve this using CASE statement and second is to use IIF Sql logical function (available in sql 2012 and higher version)

Implementation: Let’s understand using suitable example.

Create an employee table 'tbEmployee' using the script below:

CREATE  TABLE tbEmployee
(
   EmployeeID            INT PRIMARY KEY IDENTITY(1,1),
   EmployeeName      VARCHAR(50),
   Gender                    CHAR(1)        
)

Insert some dummy data into this table. 

INSERT INTO tbEmployee 
VALUES
('Simran','F'),
('Anuj','M'),
('Vikas','M'),
('Rohan','M') ,
('Swati','F')

Check table data
SELECT * FROM tbEmployee

EmployeeId
EmployeeName
Gender
1
Simran
F
2
Anuj
M
3
Vikas
M
4
Rohan
M
5
Swati
F

Now lets get the result we want:

Using CASE Statement:

SELECT EmployeeName, (CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END) AS Gender
FROM tbEmployee

Using IIF logical function:

SELECT EmployeeName, IIF(Gender = 'M' ,'Male','Female') AS Gender
FROM tbEmployee

Result will be what we want: 😊
EmployeeName
Gender
Simran
Female
Anuj
Male
Vikas
Male
Rohan
Male
Swati
Female

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..