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
