August 4, 2014 at 7:23 am
When writing an expression in SSRS 2008 to check for blank values (NULL), is there any difference between Fields!FieldName.Value = Nothing and Fields!FieldName.Value Is Nothing? It seems that i have seen it both ways. I am currently using = Nothing and it seems to be working.
Thanks
PK
August 4, 2014 at 7:46 am
paul.j.kemna (8/4/2014)
When writing an expression in SSRS 2008 to check for blank values (NULL), is there any difference between Fields!FieldName.Value = Nothing and Fields!FieldName.Value Is Nothing? It seems that i have seen it both ways. I am currently using = Nothing and it seems to be working.Thanks
PK
Generally speaking, "equals nothing" or "= null" is not a proper expression in SQL Server or in any ANSI compliant relational database. You can't really compare "nothing" to something else. For example, you can say that a glass is empty, but you can say that a glass equals empty since the state of emptyness is not an exact measurement for comparison.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2014 at 10:54 am
I guess i will play it safe and change these to IS Nothing rather than = Nothing, even though it appears to work.
I also occasionally use LEN(Fields!FieldName.Value) > 0) in expressions to check for truly blank (NULL) fields. Are there any pitfalls to this method?
PK
August 4, 2014 at 11:06 am
paul.j.kemna (8/4/2014)
I guess i will play it safe and change these to IS Nothing rather than = Nothing, even though it appears to work.I also occasionally use LEN(Fields!FieldName.Value) > 0) in expressions to check for truly blank (NULL) fields. Are there any pitfalls to this method?
PK
Keep in mind that LEN() ignores trailing spaces, and the length of NULL is NULL.
len(null) is null
len('') = 0
len(' ') = 0
len('x ') = 1
You may want to use one of the techniques listed here. Perhaps use ISNULL() or COALESCE() to convert NULL to empty string '' within SELECT statement.
http://stackoverflow.com/questions/835954/sql-2005-reporting-services-if-check-for-null
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2014 at 1:26 pm
The SSRS expression language is basically VB.
According to the MSDN page on Nothing:
When checking whether a reference (or nullable value type) variable is null, do not use = Nothing or <> Nothing. Always use Is Nothing or IsNot Nothing.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2014 at 8:16 pm
When checking value or reference types, I've gotten better use out of the basic comparison operators, especially "=". You can test this with any of the nullable types in SSRS pretty quickly. They work the same as the Is, IsNot, and Not(<expression> Is <value>) operators, and have the added advantage of being able to catch the default values for the type as well as null. So, for instance, for an integer (nullable) value, = Nothing will catch when the value is null or 0. The same is true for all of the basic types that can be associated with the parameters, though I haven't figured out (or needed) the default value for the DateTime type yet. I came across this when trying to check a string value for Nothing, and when the string was the empty string, Is Nothing failed. This makes sense, but wasn't what I was hoping for. You can see an example of this on the same page that has the quote
"When checking whether a reference (or nullable value type) variable is null, do not use = Nothing or <> Nothing. Always use Is Nothing or IsNot Nothing."
Here's how that example reads,
"For strings in Visual Basic, the empty string equals Nothing. Therefore, "" = Nothing is true."
You'll find that written one line below the first quote:)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply