Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is Nothing vs = Nothing


Is Nothing vs = Nothing

Author
Message
paul.j.kemna
paul.j.kemna
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 151
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
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4647 Visits: 9579
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
paul.j.kemna
paul.j.kemna
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 151
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
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4647 Visits: 9579
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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16518 Visits: 13210
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
dcten4
dcten4
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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>Wink 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 quoteSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search