IIF in sql server

[OhioSteve]

MS Access has this function:
IIF(expr1,expr2,expr3)
If expr1 evaluates to true, the function returns expr1, otherwise it returns expr2
So IIF(1=2,"yes","no") would return "no".

How can I do something similar in SQL Server without using transact SQL? I am just writing a normal SQL statement, not a stored procedure.

 

[SQLDenis]

lookup CASE in BOL

example

declare @z int
select @z = 1
select CASE @z when 2 then 'NO' else 'YES' end as Col

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

You have to use Transact SQL when programming in SQL Server - that is its language. TSQL and Stored Procedures are different things. One is a language and the other is a way to 'bundle' or 'package' code.

Follow SQLDenis's directions and you'll be able to do the same thing as Access' JET-SQL's IIF.

-SQLBill

Posting advice: FAQ481-4875

 

[earthandfire]

There is one very important difference between IIF and the CASE statement as demonstrated by Denis:

Irrespective of whether or not the test expression is true, BOTH alternatives in an IIF are evaluated, but only the appropriate one is returned.


Hope this helps.

 

[Catadmin]

You could use a regular IF...ELSE statement in T-SQL (look it up in Books Online). It's the closest T-SQL has to the IIF function.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[GeertVerhoeven]

Correct Catadmin.

If you need to use an IIF within a stored procedure/function, you can use the IF .. ELSE statements.

When using it in a query, you need to go for the CASE WHEN statements.

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog