Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is Nothing vs = Nothing Expand / Collapse
Author
Message
Posted Monday, August 4, 2014 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
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
Post #1599248
Posted Monday, August 4, 2014 7:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:37 AM
Points: 1,702, Visits: 4,837
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.
Post #1599261
Posted Monday, August 4, 2014 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:40 AM
Points: 48, Visits: 117
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
Post #1599337
Posted Monday, August 4, 2014 11:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:37 AM
Points: 1,702, Visits: 4,837
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
Post #1599342
Posted Monday, August 4, 2014 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 13,238, Visits: 11,014
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.


Nothing (Visual Basic)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1599378
Posted Thursday, August 21, 2014 8:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:54 PM
Points: 1, Visits: 2
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:)
Post #1606091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse