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.

In previous article i explained Using case statement inside IN clause (alternative) and Case expression in select statement in sql server and Using merge in sql server to insert, update and delete in single statement and Remove duplicate records/data from sql server database table and Using CTE to get all dates between two specified dates

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. 

• Tweet
• Share
• Share
• Share

Related Post