Can Iif be used to change query result?

I'm converting a set of queries from Access to work as stored procedures on SQL server, and one of them that uses IIF gives me a syntax error. Here's the WHERE clause of the SELECT:

WHERE IIF(@.myExtNum > 0,D.ExtentionNumber=@.myExtNum,'') AND ...

I get the error message "syntax error near '>'."

It would seem this query wants to do two things: 1) if @.myExtNum is >0, return only the rows for which D.ExtentionNumber equals a user specified value, or 2) if @.myExtNum is 0 ignore this part of the condition.

I can rewrite this using multiple ANDs and ORs, but I wodered if there was a way I could get IIF working.

Something in my gut tells me this is not going to work, that IIF is being used to modify the query; that is, change which rows are returned; it is not being used to change how a given data value is displayed, which I think was the purpose for which IIF was originally intended.

Does anyone have an insight on this?

Hello,

Sorry, but IIF isn't a valid T-SQL function. You could do this instead (if D.ExtentionNumber is not nullable):

where D.ExtentionNumber =

case

when @.myExtNum > 0 then @.myExtNum

else D.ExtentionNumber

end

If it is nullable, do this instead (the -1 is an arbitrary value that is not available to the field):

where coalesce(D.ExtentionNumber, -1) =

case

when @.myExtNum > 0 then @.myExtNum

else coalesce(D.ExtentionNumber, -1)

end

Hope this helps.

Jarret