It can happen more quickly than you think… even in environments which seem to follow an architectural role model, some dusty corners remain.
We are running an MS SQL Server Reporting Services server for some internal reports on the Oracle production database. Nice enough report generator. Now, I have done more than just some MS Access in prehistoric times, and constantly feel reminded of that “put these 120 line SQL query into the puny little properties textbox and it will do what you want” type of development. Things have definitely improved over that with MS Visual Studio 2008 and the report designer, but then, without prior notice, I was struggling with Visual Basic…
I was trying to make a text output for the report header, formatting a string parameter as a date, and the current date in case the input parameter was NULL. Simple enough? Have a look:
-
=IIf(isNothing(Parameters!enddate.Value),
-
DateTime.Now,
-
DateTime.ParseExact(Parameters!enddate.Value, "yyyyMMdd", Nothing)
-
)
Looks good, does it? Should work, shouldn’t it? Doesn’t.
DateTime.ParseExact() doesn’t like to be handed a null (VisualBasic: “Nothing”) value, and the IIF() function can’t prevent it. It actually is just a function as any other function, and before it gets called, all parameters have to be evaluated: Thus the DateTime.ParseExact is executed despite that was what I was trying to prevent. The “Nothing” check is futile, DateTime.ParseExact() is evaluated before IIF() is called, and throws the exception anyway. Feels like a “real” ternary operator, but is just a function.
The fix needs to introduce a guaranteed non-null date string even if it is never used:
-
=IIf(isNothing(Parameters!enddate.Value),
-
DateTime.Now,
-
DateTime.ParseExact(IIf(isNothing(Parameters!enddate.Value),
-
‘19991231′,
-
Parameters!enddate.Value),
-
"yyyyMMdd", Nothing)
-
)
Alternatively, you could convert the Now value back into a string, to pass into ParseExact. Ah, the hoops they make us jump through!
There is an equivalent problem in Oracle, if you will: COALESCE() will stop evaluating once it found a non-null value, NVL() will first evaluate the second argument as well.
