How to use ternary operator in SQL Server 2008?

Question

SQL query:

SELECT * 
FROM Account 
WHERE (type <>100000002 ? Id='something': Id=null)

but it shows error :

Incorrect syntax near '?'

Please help me.

if you need the example or CASE or IF Else then let me know i will send you the example — Developerzzz, Feb 03 '14 at 11:45
`where (type <>100000002 and Id='something') or (type =100000002 and id is null)` — Royi Namir, Feb 03 '14 at 11:51
IS this what you are looking for http://stackoverflow.com/questions/16209751/is-there-a-ternary-operator-in-t-sql — Punter015, Feb 03 '14 at 11:51
@Punter015 no it's not. he has null there. ( unless you can do `=null`) — Royi Namir, Feb 03 '14 at 11:51

score 21 · Accepted Answer · edited Jul 23 '17 at 04:26

21

This is for SQL Server. IIF is not available in SQL Server 2008 or earlier.

 SELECT * 
    FROM Account 
    WHERE 
    Id=IIF(type<> 100000002,"something",null )

If you are using SQL Server 2008 or earlier, then try this.

  SELECT * 
    FROM Account 
    WHERE (Id= CASE WHEN type <>100000002 THEN 'something'  ELSE null END)

edited Jul 23 '17 at 04:26

Sal Rahman

answered Oct 14 '14 at 10:27

Suman Banerjee

1

If you consider best practices you should avoid using IIF because its a function and it is slower than CASE WHEN statement – dejjub-AIS May 09 '16 at 10:09

Royi Namir · Answer 2 · 2014-02-03T12:00:16.333

5

You can do this :

SELECT * 
FROM Account 
where (type <>100000002 and Id='something') or (type =100000002 and id is null)

or

SELECT * 
FROM Account 
where  isnull(id,'_null_')= case when type <>100000002 then 'something' else  isnull(id,'_null_') end

edited Feb 03 '14 at 12:00

answered Feb 03 '14 at 11:53

Royi Namir

1

+1 for the first T-SQL, Simple T-SQL for Simple Logic – Punter015 Feb 03 '14 at 12:16

score 2 · Answer 3 · answered Feb 03 '14 at 11:54

2

SELECT * 
FROM Account 
WHERE (Id= CASE WHEN type <>100000002 THEN 'something'  ELSE null END)

answered Feb 03 '14 at 11:54

Krishnraj Rana

you cant always do `id=null` – Royi Namir Feb 03 '14 at 11:55
2

However, note that with ANSI nulls, this will probably not work as intended, since `null = null` is actually false. You have to use `Id is null` instead. – Luaan Feb 03 '14 at 11:55

Developerzzz · Answer 4 · 2016-06-08T08:17:50.080

1

Use the following:

SELECT * 
FROM Account 
WHERE (Id= CASE WHEN type != 100000002 THEN 'something'  ELSE null END)

or

SELECT * 
FROM Account 
WHERE (Id= CASE WHEN type <> 100000002 THEN 'something'  ELSE null END)

edited Jun 08 '16 at 08:17

answered Feb 03 '14 at 12:06

Developerzzz

score 1 · Answer 5 · answered Feb 04 '14 at 13:25

1

SELECT * 
FROM Account 
where (type <>100000002 and Id='something') or (type =100000002)

answered Feb 04 '14 at 13:25

System

score 0 · Answer 6 · answered Feb 03 '14 at 12:02

0

answered Feb 03 '14 at 12:02

Rahul

6 Answers6