Search

Saturday, January 7, 2012

What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?

CASE is the equivalent of IIF function. See SQL Server Books Online for more information. Here's a quick example:
CREATE TABLE People
(
[ID] int PRIMARY KEY,
[Name] varchar(25) NOT NULL,
Sex bit NULL
)

INSERT INTO People ([ID],[Name], Sex) VALUES (1,'John Dykes', 1)
INSERT INTO People ([ID],[Name], Sex) VALUES (2,'Deborah Crook', 0)
INSERT INTO People ([ID],[Name], Sex) VALUES (3,'P S Subramanyam', NULL)

SELECT [ID], [Name],
CASE Sex
WHEN 1
THEN 'Male'
WHEN 0
THEN 'Female'
ELSE 'Not specified'
END AS Sex
FROM People

No comments:

Post a Comment