Is there a ternary conditional operator in T-SQL?

Question

What are alternatives to implement the following query:

select *  
from table  
where isExternal = @type = 2 ? 1 : 0

@JFA - no, *a* ternary operator is any operator that takes three operands. In most languages, *if* they have any ternary operators, it's common that they have only one, that is (generally) called the conditional operator. Calling things by the wrong name (or using a general name when you mean a specific) can just lead to more confusion. For further guidance, please consult the [tag:ternary-operator] tag wiki. — Damien_The_Unbeliever, Jul 08 '16 at 18:26
@Damien_The_Unbeliever https://en.wikipedia.org/wiki/Ternary_operation : see "THE ternary operator" — Timothy Kanski, Dec 22 '16 at 14:49
OP used the common name for a thing, not its wrong or too-general name. I disagree with your conclusion that professionals (for whom this site was built) will be confused by this, when it is indeed commonly used terminology. — Timothy Kanski, Dec 26 '16 at 04:20
@TimothyKanski, while Damien may be coming off as a bit of a smart ass, the OP did specifically ask for "a ternary operator" not "THE ternary operator". ¯\\_(ツ)_/¯ — BrainSlugs83, Oct 26 '18 at 19:45

score 213 · Answer 1 · edited Jul 17 '17 at 12:36

213

In SQL Server 2012, you could use the IIF function:

SELECT *
FROM table
WHERE isExternal = IIF(@type = 2, 1, 0)

Also note: in T-SQL, the assignment (and comparison) operator is just = (and not == - that's C#)

edited Jul 17 '17 at 12:36

Kols

answered Apr 25 '13 at 08:35

marc_s

score 151 · Accepted Answer · answered Apr 25 '13 at 08:22

151

Use case:

select *
from table
where isExternal = case @type when 2 then 1 else 0 end

answered Apr 25 '13 at 08:22

Guffa

2 Answers2