Sql 2005 alternate of Access query

Question

Hi I am stuck in the following query which I have written for Access and working properly. But when I am running it through SQL 2005, it gives me error (Incorrect syntax near the keyword 'IS').

I have been through similar questions but there was no solution to my problem.

Here is the Access query.

select iif(ISBN IS Null,"1","0") as OK from products 

Please need a SQL 2005 version.

This might be a basic query but i am new to sql.

Thanks in advance.

Answer 1

use CASE instead.

SELECT CASE 
            WHEN ISBN IS Null
            THEN 1
            ELSE 0
       END AS OK 
FROM   products 

but IIF should work if you are using SQL Server 2012.

UPDATE

SELECT CASE 
            WHEN expression1
            THEN 0
            ELSE 
                CASE 
                    WHEN expression2
                    THEN 2
                    ELSE 3
                END
       END AS OK 
FROM   products 

Answer 2

You will need to you a CASE expression to replace the IIf(). SQL Server 2005 does not have an IIF() function:

select case when ISBN is null then 1 else 0 end as OK
from products

If you have additional IIF() statements then you can nest them:

select 
  case 
     when ISBN is null 
     then 1 
     else 
       case when yourCol = "Value"
            then 2
            else 3
       end 
  end as OK
from products