Behavioral Difference of IIf Function in T-SQL Compared To SSRS
Context
IIf function was a new addition in SQL Server 2012 which provides a simplified way to implement conditional logic. Though we have a similar function available in SSRS from 2005 version onwards there is a small difference in the implementation between the IIf function in SSRS and that in T-SQL.
This article discusses with a few illustrations to show the difference in implementation of **IIf **in T-SQL as compared to that in SSRS
Illustration
Bitwise Boolean Checks
Consider the below sample data
declare @t table
(
OrderNo int,
OrderPrice decimal(15,2),
WebOrder bit
)
INSERT @t
VALUES (12234,1354.00,0),
(17654,2145.00,0),
(21098,951.00,1),
(20067,876.00,0),
(23356,123.00,1),
(31245,987.00,0)
Now let the requirement be to show the category value in text based on the flag value.
Since WebOrder is a boolean field we tried using an expression like below
SELECT OrderNo,OrderPrice,IIF(WebOrder,'Web Order','Manual Order') AS OrderCategory
FROM @t
And this is what we got as the result
Msg 4145, Level 15, State 1, Line 16
An expression of non-boolean type specified in a context where a condition is expected, near '('.
Rewriting it as below
SELECT OrderNo,OrderPrice,IIF(WebOrder = 1 ,'Web Order','Manual Order') AS OrderCategory
FROM @t
gave us the below result
Try the same thing in an SSRS report and you will see that boolean expression check works for IIf inside SSRS expression
This clearly shows that behavior of IIf in T-SQL is not exactly the same as that inside SSRS expressions as the former will not support bitwise boolean checks.
As such always make sure you write complete expression inside T-SQL rather than relying upon bit-wise boolean operations as it doesn't work in T-SQL as per the above illustration.
Nesting Level
Now try a query as below and see the result in T-SQL
SELECT OrderNo,OrderPrice,IIF(WebOrder = 1 ,'Web Order','Manual Order') AS OrderCategory,
IIF(Cat = 1,'Cat 1',
IIF(Cat = 2,'Cat 2',
IIF(Cat = 3,'Cat 3',
IIF(Cat = 4,'Cat 4',
IIF(Cat = 5,'Cat 5',
IIF(Cat = 6,'Cat 6',
IIF(Cat = 7,'Cat 7',
IIF(Cat = 8,'Cat 8',
IIF(Cat = 9,'Cat 9',
IIF(Cat = 10,'Cat 10',
IIF(Cat = 11,'Cat 11',
IIF(Cat = 12,'Cat 12','None')))))))))))
FROM @t
You will notice the below
Msg 125, Level 15, State 2, Line 28
Case expressions may only be nested to level 10.
Try a similar logic in SSRS as below
=IIF(Fields!Cat.Value =1 ,"Cat 1",
IIF(Fields!Cat.Value =2,"Cat 2",
IIF(Fields!Cat.Value =3 ,"Cat 3",
IIF(Fields!Cat.Value =4 ,"Cat 4",
IIF(Fields!Cat.Value =5 ,"Cat 5",
IIF(Fields!Cat.Value =6 ,"Cat 6",
IIF(Fields!Cat.Value =7 ,"Cat 7",
IIF(Fields!Cat.Value =8 ,"Cat 8",
IIF(Fields!Cat.Value =9 ,"Cat 9",
IIF(Fields!Cat.Value =10 ,"Cat 10",
IIF(Fields!Cat.Value =11 ,"Cat 11",
IIF(Fields!Cat.Value =12 ,"Cat 12","None"))))))))))))
This will provide you with the below output
which shows that the nesting limit of 10 is only applicable in T-SQL.
This is because internally IIf is translated as a CASE..WHEN construct in T-SQL which is why it puts the nesting level limit as 10 which is the same for CASE..WHEN as specified by the error message.
Conditional Branch Evaluation
There's also difference in the way the conditional branches are evaluated inside IIf in T-SQL and in SSRS
Consider the below illustration
DECLARE @x int = 125,@y int = 0
SELECT IIF(@y=0,0,@x/@y)
The above query will give you output as 0 in T-SQL
Now see this similar illustration in SSRS and you will find that it shows an Error.
This is because IIf function in SSRS evaluates the expressions in both the conditional branches regardless of the actual result of the condition. Hence in SSRS the expression needs to be rewritten as below
=IIF(IsNothing(Fields!ID1.Value/IIF(Fields!ID2.Value=0,Nothing,Fields!ID2.Value)),0,Fields!ID1.Value/IIF(Fields!ID2.Value=0,Nothing,Fields!ID2.Value))
Summary
As seen from the above illustrations the implementation of IIf function in T-SQL differs from that in SSRS in the following aspects
| T-SQL | SSRS |
|
|
|
|
|
|
|
|